strftime function pandas - Python
<p>I am trying to strip the time out of the date when the dataframe writes to excel but have been unsuccessful. The weird thing is, i am able to manipute the dataframe by using the lambda and striptime function below and can see it the function is working by my 'print df6'. However, for some reason when I open the excel file it shows in the format : '%m%d%y: 12:00am ' I would liek to just have the date show. Ideas?</p>

<p>Here is my code:</p>

<pre><code>df6 = df
workbook = writer.book
df6.to_excel(writer, sheet_name= 'Frogs', startrow= 4, index=False)
df6['ASOFDATE'] = df6['ASOFDATE'].apply(lambda x:x.date().strftime('%d%m%y'))
worksheet = writer.sheets['Frogs']
format5 = workbook.add_format({'num_format': '0.00%'})
#format6= workbook.add_format({'num_format': 'mmm d yyyy'})
#worksheet.set_column('A:A', 18, format6)
worksheet.set_column('B:B', 18, format5)
worksheet.set_column('C:C', 18, format5)
worksheet.set_column('D:D', 18, format5)
now = datetime.now()
date= dt.datetime.today().strftime("%m/%d/%Y")
link_format = workbook.add_format({'color': 'black', 'bold': 1})
nrows = df6.shape[0]
worksheet.write(0, 0, 'Chart4', link_format) 
worksheet.write(1, 0, 'Frogs',link_format) 
#Multiply dataframe by 100 to get out of decimals (this method is used when dates are in the picture)
df6[df6.columns[np.all(df6.gt(0) &amp; df6.lt(1),axis=0)]] *= 100
print df6
writer.save() 
</code></pre>

<p>strftime is creating a textual representation of a datetime value which later is formatted and displayed by Excel according to its formatting templates. We could change this formatting template using <code>date_format</code> parameter in our ExcelWriter constructor e.g.:<code>writer = pandas.ExcelWriter("test.xlsx", engine="xlsxwriter", date_format="ddmmyyyy")</code>. I've slightly modified your sample:</p>

<pre><code>import pandas as pd
import datetime
df = pd.DataFrame()
df['ASOFDATE'] = [datetime.datetime.now() for x in xrange(3)]
for i in xrange(3):
    df[i] = [1,2,3]
df6 = df
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter', date_format="ddmmyyyy")
workbook = writer.book
df6.to_excel(writer, sheet_name= 'Frogs', startrow= 4, index=False)
worksheet = writer.sheets['Frogs']
format5 = workbook.add_format({'num_format': '0.00%'})
worksheet.set_column('B:B', 18, format5)
worksheet.set_column('C:C', 18, format5)
worksheet.set_column('D:D', 18, format5)
link_format = workbook.add_format({'color': 'black', 'bold': 1})
worksheet.write(0, 0, 'Chart4', link_format)
worksheet.write(1, 0, 'Frogs',link_format)
writer.save()
</code></pre>

<p>This code produces a file which has date values formatted according to the specified template.</p>

python pandas