XlsxWriter, Pandas - can't format datetime to 'dd/mm/yy'

Question

based on the Working with Dates and Time page, I can format datetime to 'dd/mm/yy' and others format, but it does not change anything:

df = pd.read_excel("\\test.xlsx", sheet_name='test')
df['Col1'] = pd.to_datetime(df['Col1']) #dtype: datetime64[ns]

writer = pd.ExcelWriter("\\test1.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='test', index=False)

book = writer.book
sheet = writer.sheets['test']
format_c = book.add_format({'font_color' : 'black', 'num_format' : 'dd/mm/yy'})
sheet.set_column(0,0,14,format_c)
writer.close()

What I get in output (same as input):

Desired output: 01/01/21

Could you, please, tell me, what I am doing wrong?

Answer 1

Your code is not reproducible, so I can't test for myself, but you are not calling sheet.write() before closing writer, so It seems that nothing is written in test1.xlsx in the end.

With the following test.xlsx file:

Here is another way to do it:

df = pd.read_excel("test.xlsx", sheet_name="test")
df["Col1"] = pd.to_datetime(df["Col1"])

with pd.ExcelWriter(
    path="test1.xlsx", engine="xlsxwriter", datetime_format="dd/mm/yy"
) as writer:
    df.to_excel(excel_writer=writer, sheet_name="test", index=False)

Then, in test1.xslx: