XLSXwriter and Short Date in Excel

Question

I have written a script that splits a few txt files and stores them into xlsx files to be uploaded to another application. The excel files have a column B that NEEDS TO BE IN SHORT DATE FORMAT or else the upload fails. I have tested this rigorously. Ive tried everything I can think of. Here is an example of what is happening.

Date
03/31/2021

The data aboveis what my python file outputs. I need this to be in short date in excel. I notice the output comes out indented to the left which is odd for excel. When I click into a cell in this column and hit enter it comes out like this. It will now be in Short date format and can be uploaded. I have no idea how to format this column into the proper short date.

Date
3/31/2021

Thank you in advance for any assistance, I need to get column B into short date format from python.

EDIT showing script:

This is the script im using

df['CYCLE_DATE']=df['CYCLE_DATE'].dt.strftime('%m/%d/%Y') 

this line changes the txt file output. The TXT file gives something like this 3/31/2021 0:00:00

Ive tried something like this as well

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

tdf.to_excel(writer, sheet_name='s1', index=None, header=True)


workbook  = writer.book
worksheet = writer.sheets['s1']

formatdict = {'num_format':'mm-dd-yy'}
fmt = workbook.add_format(formatdict)
worksheet.set_column('B:B', None, fmt)


writer.save()

Does not work.

Procedure

Can you show us your script? – Russom

See my updated example below. – Wadai

Answer 1

I notice the output comes out indented to the left which is odd for excel.

Yes and no. In Excel strings are aligned to the left and numbers are aligned to the right (if no other cell alignment is applied). And also in Excel dates are numbers, not strings, which means that the “date” is probably a string and not a date. Hence the upload failure.

A date in Excel also needs to have a number format like “yyyy/mm/dd” or else it will appear just as a number (which again it is, technically).

In order to create a date with XlsxWriter (or Excel) you will need to convert the date to a number and apply a format. This is most easily done if the dates are Python datetime objects. This is explained in detail in the Working with Dates and Time section of the XlsxWriter docs.

Update after the example was added to the question.

The reason the xlsxwriter/Excel column formatting isn't working is that Pandas has already used a cell format to format the date and that can't be overridden by a column format. You can verify that the formatting is actually there by adding a number into a blank cell in the formatted column - it will show the number in the date format you specified.

The workaround for this is to specify the date format that you want in the Pandas ExcelWriter object. Like this:

import pandas as pd
from datetime import datetime

df = pd.DataFrame({'CYCLE_DATE': [datetime(2021, 1, 1, 11, 30, 55),
                                  datetime(2021, 1, 2, 1,  20, 33),
                                  datetime(2021, 1, 3, 11, 10, 19),
                                  datetime(2021, 1, 4, 16, 45, 35),
                                  datetime(2021, 1, 5, 12, 10, 15)]})

writer = pd.ExcelWriter('test.xlsx',
                        engine='xlsxwriter',
                        datetime_format='mm-dd-yy')

df.to_excel(writer, sheet_name='s1', index=None, header=True)

writer.save()

Output:

See also this section of the XlsxWriter docs: Formatting of the Dataframe output.

Wadai