2

I have an xlsx file with multiple tabs, each tab has a Date column in the format of MM/DD/YYYY

Read each tab into a pandas dataframe, applied some operations on each tab, and then write the dataframe back into two formats: xlsx and csv

In the xlsx file, the Date column (index) becomes a format that has the time attached: 1/1/2013 12:00:00 AM, while the Date column in the csv file remains unchanged: MM/DD/YYYY

How can I make sure the Date column in the xlsx file maintains the same format MM/DD/YYYY?

Merlin
  • 24,552
  • 41
  • 131
  • 206
Kevin
  • 2,191
  • 9
  • 35
  • 49

2 Answers2

1

You are seeing the datetime in the default pandas Excel datetime format. However, you can easily set it to whatever you want:

# Set the default datetime and/or date formats.
writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        date_format='mm/dd/yyy',
                        datetime_format='mm/dd/yyyy')

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

See a full example in the XlsxWriter docs: Example: Pandas Excel output with date times.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
0

You can convert the date to a string. If the date is in the index, you could do:

df.set_index(df.index.map(lambda x: x.strftime('%m/%d/%Y'))).to_excel()
piRSquared
  • 285,575
  • 57
  • 475
  • 624