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.

Date Format

The set_num_format() Method

Since 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. 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 like the following in Excel software −

Num Format Parameter

write_datetime() and strptime()

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

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 −

%a

Abbreviated weekday name

Sun, Mon

%A

Full weekday name

Sunday, Monday

%d

Day of the month as a zero-padded decimal

01, 02

%-d

day of the month as decimal number

1, 2..

%b

Abbreviated month name

Jan, Feb

%m

Month as a zero padded decimal number

01, 02

%-m

Month as a decimal number

1, 2

%B

Full month name

January, February

%y

Year without century as a zero padded decimal number

99, 00

%-y

Year without century as a decimal number

0, 99

%Y

Year with century as a decimal number

2022, 1999

%H

Hour (24 hour clock) as a zero padded decimal number

01, 23

%-H

Hour (24 hour clock) as a decimal number

1, 23

%I

Hour (12 hour clock) as a zero padded decimal number

01, 12

%-I

Hour (12 hour clock) as a decimal number

1, 12

%p

locale's AM or PM

AM, PM

%M

Minute as a zero padded decimal number

01, 59

%-M

Minute as a decimal number

1, 59

%S

Second as a zero padded decimal number

01, 59

%-S

Second as a decimal number

1, 59

%c

locale's appropriate date and time representation

Mon Sep 30 07:06:05 2022

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, 'align': '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.

Datetime And Strptime
Advertisements