Region / locale / user settings specific date formatting – PyXLL

Using PyXLL's cell formatting feature we can easily format dates returned from Python to Excel.

But, the date format is specified as an Excel "number format", for example "yyyy-mm-dd", and so is fixed and doesn't change based on the Excel user's system date/time settings or Excel locale settings.

To format a date that changes depending on the locale of the current user we need to use a "custom formatter" that will determine the correct number format that will show the date correctly for the user.

Writing a custom formatter

Writing a custom formatter simply needs us to declare a new class that derives from PyXLL's "Formatter" class.

from pyxll import Formatter

class LocaleDateFormatter(Formatter):

    # We override the 'apply' method but don't need all of the arguments

    def apply(self, cell, value, *args, **kwargs):

        # Here will will apply the formatting we need using 
        # the "Formatter.apply_style" method.

        # TODO!

        pass

Getting the right date format

We can get the date formatting information we need using Excel's Application.International property. To access this from Python we use PyXLL's "xl_app" function to get the Excel Application object.

Note: PyXLL's xl_app function can only be called from within an Excel macro. As the Formatter.apply method is called from an Excel macro it is safe to use it here.

The "International" property takes an argument, but in Python properties can never take an argument, so instead we use the "GetInternational" accessor method on the Python Application object.

The constants we need can be found in the win32com.client.constants package.

See https://www.pyxll.com/docs/userguide/vba.html for more information on these details about translating VBA code to Python.

from pyxll import xl_app

from win32com.client import constants

def get_date_format():

    """Get the Excel number format for displaying a date,
    taking the Excel International settings into account.

    This function uses pyxll.xl_app so can only be called
    from within an Excel macro.
    """

    # Get the Excel Application object

    xl = xl_app()

    # Get the strings we need to build the number format

    y = xl.GetInternational(constants.xlYearCode)

    m = xl.GetInternational(constants.xlMonthCode)

    d = xl.GetInternational(constants.xlDayCode)

    # And the m/d/y ordering and separator

    order = xl.GetInternational(constants.xlDateOrder)

    sep = xl.GetInternational(constants.xlDateSeparator)
 
    # Build and return the number format

    # 0 = month-day-year, 1 = day-month-year, 2 = year-month-day

    if order == 0:

        return f"{m}{m}{sep}{d}{d}{sep}{y}{y}{y}{y}"

    elif order == 1:

        return f"{d}{d}{sep}{m}{m}{sep}{y}{y}{y}{y}"

    elif order == 2:

        return f"{y}{y}{y}{y}{sep}{m}{m}{sep}{d}{d}"

    raise ValueError(f"Unexpected value '{order}' for xlDateOrder.")

Putting it all together

Now we know how to write a custom formatter, and how to get the number format string we need to format a cell as a date, we can put the two together:

class LocaleDateFormatter(Formatter):

    # We override the 'apply' method but don't need all of the arguments

    def apply(self, cell, value, *args, **kwargs):

        # Get the date format

        date_format = get_date_format()

        # Apply it as the number format for the cell

        self.apply_style(cell, {

            "number_format": date_format
        })

Applying the custom formatter to a function

Our new custom formatter works in exactly the same way as the standard formatters and can be applied to a function using the "formatter" kwarg to the @xl_func decorator.

Formatters can also be combined by adding them together. For example, if you wanted to a formatter that would make the cell bold as well as applying date formatting you could do the following:

from pyxll import xl_func

import datetime as dt

# Create a bold formatter and a date formatter

bold_formatter = Formatter(bold=True)

date_formatter = LocaleDateFormatter()

# Combine them to make a bold date formatter

my_formatter = bold_formatter + date_formatter

@xl_func(formatter=my_formatter)

def date_test():

    return dt.date.today()

Extra Credit - caching the date format

We don't really need to create the date format each time the formatting is applied. An easy way to cache the result of our "get_date_format" function is to add the "functools.cache" decorator to it.

from functools import cache

@cache

def get_date_format():
    ...

Now the format will be determined the first time this function is called, and subsequent calls with return the cached result.

Writing a custom formatter

Getting the right date format

Putting it all together

Applying the custom formatter to a function

Extra Credit - caching the date format

Related articles