Python XlsxWriter - Date & Time

In Excel, dates are stored as real numbers so that they can be used in calculations. By default, January 1, 1900 (called as epoch) is treated 1, and hence January 28, 2022 corresponds to 44589. Similarly, the time is represented as the fractional part of the number, as the percentage of day. Hence, January 28, 2022 11.00 corresponds to 44589.45833.

The set_num_format() Method

Since date or time in Excel is just pke any other number, to display the number as a date you must apply an Excel number format to it. Use set_num_format() method of the Format object using appropriate formatting.

The following code snippet displays a number in "dd/mm/yy" format.

num = 44589
format1 = wb.add_format()
format1.set_num_format( dd/mm/yy )
ws.write( B2 , num, format1)

The num_format Parameter

Alternatively, the num_format parameter of add_format() method can be set to the desired format.

format1 = wb.add_format({ num_format : dd/mm/yy })
ws.write( B2 , num, format1)

Example

The following code shows the number in various date formats.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

num=44589
ws.write( A1 , num)

format2 = wb.add_format({ num_format :  dd/mm/yy })
ws.write( A2 , num, format2)

format3 = wb.add_format({ num_format :  mm/dd/yy })
ws.write( A3 , num, format3)

format4 = wb.add_format({ num_format :  d-m-yyyy })
ws.write( A4 , num, format4)

format5 = wb.add_format({ num_format :  dd/mm/yy hh:mm })
ws.write( A5 , num, format5)

format6 = wb.add_format({ num_format :  d mmm yyyy })
ws.write( A6 , num, format6)

format7 = wb.add_format({ num_format :  mmm d yyyy hh:mm AM/PM })
ws.write( A7 , num, format7)

wb.close()

Output

The worksheet looks pke the following in Excel software −

write_datetime() and strptime()

The XlsxWriter s Worksheet object also has write_datetime() method that is useful when handpng date and time objects obtained with datetime module of Python s standard pbrary.

The strptime() method returns datetime object from a string parsed according to the given format. Some of the codes used to format the string are given below −

The strptime() method is used as follows −

>>> from datetime import datetime
>>> dt="Thu February 3 2022 11:35:5"
>>> code="%a %B %d %Y %H:%M:%S"
>>> datetime.strptime(dt, code)
datetime.datetime(2022, 2, 3, 11, 35, 5)

This datetime object can now be written into the worksheet with write_datetime() method.

Example

In the following example, the datetime object is written with different formats.

import xlsxwriter
from datetime import datetime
wb = xlsxwriter.Workbook( hello.xlsx )
worksheet = wb.add_worksheet()

dt="Thu February 3 2022 11:35:5"
code="%a %B %d %Y %H:%M:%S"
obj=datetime.strptime(dt, code)
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 ,
)
worksheet.write( A1 ,  Formatted date )
worksheet.write( B1 ,  Format )
row = 1

for fmt in date_formats:
   date_format = wb.add_format({ num_format : fmt,  apgn :  left })
   worksheet.write_datetime(row, 0, obj, date_format)
   worksheet.write_string(row, 1, fmt)
   row += 1
wb.close()

Output

The worksheet appears as follows when opened with Excel.