nephilimrising nephilimrising - 1 month ago 6
Python Question

XlsxWriter writing date as number

I am writing date to a file and i am using:

date.strftime('%m/%d/%Y')


and i am writing date object to excel file:

worksheet.write(row_count, column_count, r, formatter)


where
formatter
is aligning it in the middle and
r
is the object in the current loop.

It shows like
10/28/2016
but i want it to be saved as a number value (like
42871
for example). Is there any way this to happen within code and not manually in excel?

Answer

As explained in the XlsxWriter documentation on Working with Dates and Times a date in Excel is just a number with a format. If you omit the format you get the number:

from datetime import datetime
import xlsxwriter

workbook = xlsxwriter.Workbook('datetimes.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column so that the dates are visible.
worksheet.set_column('A:A', 20)

# Create a datetime object.
date_time = datetime.strptime('2017-05-16', '%Y-%m-%d')

# Create a format for the date or time.
date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})

worksheet.write('A1', date_time, date_format)
worksheet.write('A2', date_time             )

workbook.close()

Output:

enter image description here

Comments