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

Question

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.

Solution 1

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

Solution 2

Not sure if this what you want, Cell B3 is the time and date you start with and the formula will adjust the hours. C3 is four hours earlier and D4 is 13 hours later.

Cell C3: =B3-(4/24)

Cell D4: =B3+(13/24)

Note that is B3 is noon, cell D4 is 1:00am the NEXT day.

Comments

[Ramhound]

Excel only uses the computer's system time. You can only control the format of the date and time within Excel. If you need UTC set the time zone on the system to UTC. If you are converting a raw file, one not generated by the Date function within excel, its up to you perform the offset calculations.

[Kendall Lister]

Thanks, I was afraid this was the case. Do you have a source confirming this that we could link to?

[Ramhound]

Microsofts own website

[Kendall Lister]

Any chance of being more specific? I might find an alternative approach if you can point me to the particular piece of documentation you're referring to. I haven't been able to find such a page myself.

[Ramhound]

I wipe my research I did over a week ago

[Kendall Lister]

Thanks, but that's just hard-coding an offset - as I said in my question, I found many suggestions to do this and it isn't a solution for me because, apart from the fact that it will fail whenever daylight saving time starts or ends, it won't allow users of the spreadsheet in different time zones to see the times in their time zones.

[Imager]

It pretty much the same as what's on the Microsoft Site, there was one guy, by a Brian Borg, with a Powershell option. Here the link, if you want to give it a go (it's over my head). Posted July 29, 2010: set PSCMD="Get-Date -Date (Get-Date -Date \"!UTC!\").ToLocalTime() !Format!" for /f "delims=" %o in ('PowerShell !PSCMD!') do set UTC=%o

[Imager]

answers.microsoft.com/en-us/msoffice/forum/…

[Kendall Lister]

Thanks for the suggestion, but that is a PowerShell command that he used while generating the data that the author then imported into Excel. It seems that as @Ramhound said above Excel just has no awareness of time zones and so converting between them within Excel is not possible. Thanks for trying to help me though!