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?

Answer 1

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: