Xlsxwriter Custom Column Formatting issue

Question

I am sure there is a more efficient way to write the code but the issue i am having is in my dataframe 2 of my columns (dtypes are objects that represent dates in format yyyy-mm-dd). When I use xlsxWriter everything else is coming out exactly as i need except the two columns that have dates are 'custom' column types in excel and aren't formatted (align top nor is the size 8). The rest of the columns in excel are 'general' and formatting is correct. Any suggestions would be greatly appreciated

writer=pd.ExcelWriter("file.xlsx", engine='xlsxwriter')
df_summary.to_excel(writer, (sheet_name)='Sheet1', startrow=15, header=True, index=False)

workbook=writer.book
worksheet=writer.sheets['Sheet1']
worksheet.set_column('A:O', 15)  
worksheet.set_column('P:R', 30) 
workbook.formats[0].set_font_size(8)  
workbook.formats[0].set_text_wrap() 
workbook.formats[0].set_align('top') 
bg_format=workbook.add_format({'bg_color': '#cccccc'})
worksheet.set_row(15, cell_format=(bg_format))
writer.save()

Answer 1

If your columns with the dates are objects you firstly need to convert them to datetime. You then need to specify the default date format when you create the writer object with xlsxwriter (for more info have a look here).

Here is a working example to demonstrate this:

import pandas as pd

df = pd.DataFrame({'Date': ['04/23/2020','04/24/2020',
                            '04/25/2020','04/26/2020']})

df['Date'] = pd.to_datetime(df['Date'])

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter',
                        datetime_format='m/d/yyyy')

df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

writer.save()

EDIT

import pandas as pd

df = pd.DataFrame({'Date': ['04/23/2020','04/24/2020',
                            '04/25/2020','04/26/2020']})

df['Date'] = pd.to_datetime(df['Date'])

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

df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

cellFormat = workbook.add_format({'font_size': 8,
                            'font_name': 'Calibri',
                            'valign': 'top',
                            'align': 'left',
                            'num_format': 'm/d/yyyy'})

for row in range(1,5):
    worksheet.write(row, 0, df.iloc[row-1,0], cellFormat)

writer.save()