Excel dates formats in pandas

Question

I have a dataframe that looks like this....

df2['date1'] = ""
df2['date2'] = '=IF(INDIRECT("A"&ROW())="","",INDIRECT("A"&ROW())+30)'
df2['date3'] = '=IF(INDIRECT("A"&ROW())="","",INDIRECT("A"&ROW())+35)'

I want date2 and date3 to be calculated in excel using the excel formulas. I create this dataframe in python, then save the result to excel. to save to excel, I have tried:

writer = pd.ExcelWriter("test.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

# Convert the dataframe to an XlsxWriter Excel object.
df2.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects. in order to set the column
# widths, to make the dates clearer.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Set the column widths, to make the dates clearer.
worksheet.set_column(1, max_col, 20)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

When I do this, I get an excel sheet with empty columns, when I enter the date in date1, I set serial numbers back in date2 and date3, so I know my coding is correct, and when I manually convert the format to short date, I get the correct dates in the mm/dd/yyyy format.

So my question is how do I set the format up in python so that I do not have to manually change the date format everytime this excel refreshes?

Side note, not related to your direct question: INDIRECT is volatile and should be avoided. Use INDEX: =IF(INDEX(A:A,ROW())="","",INDEX(A:A,ROW())+30) — BigBen
– BigBen, Commented Mar 16, 2022 at 18:31

jmcnamara · Accepted Answer · 2022-03-16 20:32:23Z

2

The datetime_format and date_format options to ExcelWriter() don't work because the dataframe columns don't have a datetime-like data type.

Instead you can use the xlsxwriter worksheet handle to set the column format.

Here is an adjusted version of your code to demonstrate:

import pandas as pd

# Create a sample dataframe.
df2 = pd.DataFrame({
    'date1': [44562],
    'date2': ['=IF(INDIRECT("B"&ROW())="","",INDIRECT("B"&ROW())+30)'],
    'date3': ['=IF(INDIRECT("B"&ROW())="","",INDIRECT("B"&ROW())+35)']})

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df2.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Create a suitable date format.
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})

# Get the dimensions of the dataframe.
(max_row, max_col) = df2.shape

# Set the column widths and add a date format.
worksheet.set_column(1, max_col, 14, date_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

answered Mar 16, 2022 at 20:32

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user12555533 Over a year ago

@jmcnamara...I'll play with this and will revert back, but thank you for your time.

user12555533 Over a year ago

@jmcnamara...This worked -- Thank you very much for this.

Collectives™ on Stack Overflow

Excel dates formats in pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related