Example: Dates and Times in Excel

This program is an example of writing some of the features of the XlsxWriter module. See the Working with Dates and Time section for more details on this example.

##############################################################################
#
# A simple program to write some dates and times to an Excel file
# using the XlsxWriter Python module.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2023, John McNamara, jmcnamara@cpan.org
#
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. In the output file compare how changing
# the format codes change the appearance of the date.
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()