Working with Dates and Time
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.
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()
Default Date Formatting
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()
Timezone Handling
Excel doesn’t support timezones in datetimes/times so there isn’t any fail-safe way that XlsxWriter can map a Python timezone aware datetime into an Excel datetime. As such the user should handle the timezones in some way that makes sense according to their requirements. Usually this will require some conversion to a timezone adjusted time and the removal of the tzinfo from the datetime object so that it can be passed to write_datetime() :
utc_datetime = datetime(2016, 9, 23, 14, 13, 21, tzinfo=utc) naive_datetime = utc_datetime.replace(tzinfo=None) worksheet.write_datetime(row, 0, naive_datetime, date_format)
Alternatively the Workbook() constructor option remove_timezone can be used to strip the timezone from datetime values passed to write_datetime() . The default is False . To enable this option use:
workbook = xlsxwriter.Workbook(filename, 'remove_timezone': True>)
When Working with Pandas and XlsxWriter you can pass the argument as follows:
writer = pd.ExcelWriter('pandas_example.xlsx', engine='xlsxwriter', options='remove_timezone': True>)
© Copyright 2013-2023, John McNamara.
Created using Sphinx 1.8.6.
Dates in Excel spreadsheets¶
In reality, there are no such things. What you have are floating point numbers and pious hope. There are several problems with Excel dates:
- Dates are not stored as a separate data type; they are stored as floating point numbers and you have to rely on:
- the “number format” applied to them in Excel and/or
- knowing which cells are supposed to have dates in them.
This module helps with the former by inspecting the format that has been applied to each number cell; if it appears to be a date format, the cell is classified as a date rather than a number.
Feedback on this feature, especially from non-English-speaking locales, would be appreciated.
If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).
© Copyright 2005-2019 Stephen John Machin, Lingfo Pty Ltd. 2019-2021 Chris Withers Revision 0c4e80b3 .
Versions latest stable Downloads On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.
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 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() 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() ws.write('A2', num, format2) format3 = wb.add_format() ws.write('A3', num, format3) format4 = wb.add_format() ws.write('A4', num, format4) format5 = wb.add_format() ws.write('A5', num, format5) format6 = wb.add_format() ws.write('A6', num, format6) format7 = wb.add_format() ws.write('A7', num, format7) wb.close() Output
The worksheet looks like the following in Excel software −
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 −
Day of the month as a zero-padded decimal
day of the month as decimal number
Month as a zero padded decimal number
Month as a decimal number
Year without century as a zero padded decimal number
Year without century as a decimal number
Year with century as a decimal number
Hour (24 hour clock) as a zero padded decimal number
Hour (24 hour clock) as a decimal number
Hour (12 hour clock) as a zero padded decimal number
Hour (12 hour clock) as a decimal number
Minute as a zero padded decimal number
Minute as a decimal number
Second as a zero padded decimal number
Second as a decimal number
locale’s appropriate date and time representation
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() 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.