Q:

How can I display a UTC date time value in the user's local time zone in Excel?

I have a spreadsheet with date/time values in UTC that I would like to be displayed to users in their time zone, e.g. the same spreadsheet containing UTC date/time values is provided to users in Australia and England and each user should see the date/time values converted to their local time zone.
I've found many suggestions ranging from hard-coding time offsets in formulas (e.g. B2=A1+(n/24) where n is the time zone offset) up to including a table of daylight saving time changes in the spreadsheet, but none of these approaches will allow users in different time zones to see the times as local times for them, not to mention the difficulty of maintaining up to date time zone offset and daylight saving change information.

A:

Based on @Ramhound's comments and my own research, the answer is that Excel knows nothing about time zones and so this is not possible, although I have not found a definitive statement to that effect.
This documentation page from Microsoft describes how Excel represents dates and makes no mention of time zones, implying that Excel does not support them:
https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f

Microsoft Office Excel stores dates as sequential numbers that are called serial values.

The existence of many partial solutions from outside Microsoft supports the suggestion that this is not possible, e.g.:
https://exceljet.net/formula/convert-time-to-time-zone
The answer to this question might help if VB scripting is possible, as it brings some Windows system time zone conversion functions into Excel:
https://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel
It seems that Power Query supports time zones and conversions, which may be an option for Excel users requiring this:
https://msdn.microsoft.com/en-us/library/mt296609.aspx

