techscolasticus
techscolasticus

Reputation: 97

How to preserve date format when creating an Excel file?

I have an .xlsx file that I import into Python and create a Pandas dataframe. One of the columns in the .xlsx file is formatted as a date, mm-dd-yyyy and gets imported like that. I then delete some unneeded columns from that dataframe and export that dataframe using the xlsxwriter engine to create another Excel file.

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', date_format='mm/dd/yyyy')

When I do that, the date column's format changes and time automatically gets added to the date column. The cells in the column then follow this format, yyyy-mm-dd hh:mm:ss.

I found a potential solution but when I tried the code below,

df.set_index(df.index.map(lambda x: x.strftime('%m/%d/%Y'))).to_excel()

I get the error

AttributeError: 'int' object has no attribute 'strftime'

I also read that it isn't possible to format any cells that already have a default format applied.

Is this a lost cause? How can I preserve the pre-existing date format upon export as an .xlsx file?

Upvotes: 1

Views: 2084

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

Try this:

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', 
                        date_format='mm/dd/yyyy', datetime_format='mm/dd/yyyy')

Upvotes: 6

Related Questions