Pandas: Write dates to Excel such that they are useable as Dates

Question

I cannot get my outputted XLSX to write dates in a usable fashion and I have followed familar threads like:

https://xlsxwriter.readthedocs.io/example_pandas_datetime.html

Problem with Python Pandas data output to excel in date format

Here is a MWE:

import pandas as pd
import xlsxwriter

not_in1 = ['missing']
# generate data
df = pd.DataFrame({'date1': ['5/1/2022 00:33:22', '3/1/2022 00:33:22', 'missing'], 'date2': ['3/1/2022 00:33:22', 'missing', '6/2/2022 00:33:22']})
# format
df['date1'] = df['date1'].apply(lambda x: pd.to_datetime(x).strftime('%m/%d/%Y') if x not in not_in1 else x)
df['date2'] = df['date2'].apply(lambda x: pd.to_datetime(x).strftime('%m/%d/%Y') if x not in not_in1 else x)

# write
path = 'C:\\Users\\Andrew\\Desktop\\xd2.xlsx'
with pd.ExcelWriter(path, engine='xlsxwriter', date_format="mm dd yyyy", datetime_format="mm dd yyyy") as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    formatdict = {'num_format':'mm/dd/yyyy'}
    fmt = workbook.add_format(formatdict)
    worksheet.set_column('A:B', 20, fmt)

Here as an XLSX, Excel doesn't know what to do:

https://i.stack.imgur.com/PBrAi.png

Interestingly, if I save the XLSX sheet as a CSV, the dates work just fine.

https://i.stack.imgur.com/tROFc.png

I don't think the `strftime()` conversion from string to datetime is working in your example. The type of data for the dates in the output Excel file is string and not date/number. Also, in Python the type is still string which you can see if you do `type(df['date1'][0])`. This will give `` before and after the conversion. — jmcnamara, Jul 08 '21 at 14:48

score 4 · Accepted Answer · answered Jul 08 '21 at 14:52

Your lambda function converts the x argument into a string, you should keep it as datetime instead. Currently you're ending up with a string in Excel (use Excel's type to see the difference between the .csv and .xlsx files).

Just remove the .strftime('%m/%d/%Y') and you'll be fine.

Pandas: Write dates to Excel such that they are useable as Dates

1 Answers1