Dates and times in Excel are represented by real numbers, for example “Jan 1 2013 12:00 PM” is represented by the number 41275.5.
The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day.
A date or time in Excel is just like any other number. To display the number as a date you must apply an Excel number format to it. Here are some examples:
import xlsxwriter
workbook = xlsxwriter.Workbook('date_examples.xlsx')
worksheet = workbook.add_worksheet()
# Widen column A for extra visibility.
worksheet.set_column('A:A', 30)
# A number to convert to a date.
number = 41333.5
# Write it as a number without formatting.
worksheet.write('A1', number) # 41333.5
format2 = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write('A2', number, format2) # 28/02/13
format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write('A3', number, format3) # 02/28/13
format4 = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, format4) # 28-2-2013
format5 = workbook.add_format({'num_format': 'dd/mm/yy hh:mm'})
worksheet.write('A5', number, format5) # 28/02/13 12:00
format6 = workbook.add_format({'num_format': 'd mmm yyyy'})
worksheet.write('A6', number, format6) # 28 Feb 2013
format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7) # Feb 28 2013 12:00 PM
workbook.close()
To make working with dates and times a little easier the XlsxWriter module
provides a write_datetime()
method to write dates in standard library
datetime
format.
Specifically it supports datetime objects of type datetime.datetime
,
datetime.date
, datetime.time
and datetime.timedelta
.
There are many way to create datetime objects, for example the
datetime.datetime.strptime()
method:
date_time = datetime.datetime.strptime('2013-01-23', '%Y-%m-%d')
See the datetime
documentation for other date/time creation methods.
As explained above you also need to create and apply a number format to format the date/time:
date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})
worksheet.write_datetime('A1', date_time, date_format)
# Displays "23 January 2013"
Here is a longer example that displays the same date in a several different formats:
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('datetimes.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
# Expand the first columns so that the dates are visible.
worksheet.set_column('A:B', 30)
# Write the column headers.
worksheet.write('A1', 'Formatted date', bold)
worksheet.write('B1', 'Format', bold)
# Create a datetime object to use in the examples.
date_time = datetime.strptime('2013-01-23 12:30:05.123',
'%Y-%m-%d %H:%M:%S.%f')
# Examples date and time formats.
date_formats = (
'dd/mm/yy',
'mm/dd/yy',
'dd m yy',
'd mm yy',
'd mmm yy',
'd mmmm yy',
'd mmmm yyy',
'd mmmm yyyy',
'dd/mm/yy hh:mm',
'dd/mm/yy hh:mm:ss',
'dd/mm/yy hh:mm:ss.000',
'hh:mm',
'hh:mm:ss',
'hh:mm:ss.000',
)
# Start from first row after headers.
row = 1
# Write the same date and time using each of the above formats.
for date_format_str in date_formats:
# Create a format for the date or time.
date_format = workbook.add_format({'num_format': date_format_str,
'align': 'left'})
# Write the same date using different formats.
worksheet.write_datetime(row, 0, date_time, date_format)
# Also write the format string for comparison.
worksheet.write_string(row, 1, date_format_str)
row += 1
workbook.close()
In certain circumstances you may wish to apply a default date format when
writing datetime objects, for example, when handling a row of data with
write_row()
.
In these cases it is possible to specify a default date format string using the
Workbook()
constructor default_date_format
option:
workbook = xlsxwriter.Workbook('datetimes.xlsx', {'default_date_format':
'dd/mm/yy'})
worksheet = workbook.add_worksheet()
date_time = datetime.now()
worksheet.write_datetime(0, 0, date_time) # Formatted as 'dd/mm/yy'
workbook.close()