CODEWITHSUNDEEP
pythonexcel
Gavin Jones
Gavin Jones

Reputation: 185

Python Script to convert Excel Date Format from mmm-yy to yyyy-mm-dd?

Hi can someone please assist.

I wish to modify Column A, on Sheet 3 of an Excel Work book to change the Date Format from:

Sep-15 to 2016-09-15.

In Excel this the format is mmm-yy I wish to change it to yyyy-mm-dd.

Trying to get my head around it, I know you could use a module like pandas, or xlsxwriter but the example are not making sense.

Thanks

Upvotes: 0

Views: 1283

Answers (1)

Tony Vincent
Tony Vincent

Reputation: 14292

See this example.

import xlsxwriter

workbook = xlsxwriter.Workbook('date_examples.xlsx')
worksheet = workbook.add_worksheet()

# Widen column A for extra visibility.
worksheet.set_column('A:A', 30)

# A number to convert to a date.
number = 41333.5

# Write it as a number without formatting.
worksheet.write('A1', number)                # 41333.5

format2 = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write('A2', number, format2)       # 28/02/13

format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write('A3', number, format3)       # 02/28/13

format4 = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, format4)       # 28-2-2013

format5 = workbook.add_format({'num_format': 'dd/mm/yy hh:mm'})
worksheet.write('A5', number, format5)       # 28/02/13 12:00

format6 = workbook.add_format({'num_format': 'd mmm yyyy'})
worksheet.write('A6', number, format6)       # 28 Feb 2013

format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7)       # Feb 28 2013 12:00 PM

workbook.close()

Source

Upvotes: 1

Related Questions