1

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?

techscolasticus
  • 97
  • 13

1 Answers1

6

Try this:

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', 
                        date_format='mm/dd/yyyy', datetime_format='mm/dd/yyyy')
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419