4

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.

Improve this question
5
  • 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.
    – Ramhound
    Dec 7, 2016 at 3:26
  • Thanks, I was afraid this was the case. Do you have a source confirming this that we could link to?
    – Kendall Lister
    Dec 7, 2016 at 4:06
  • Microsofts own website
    – Ramhound
    Dec 7, 2016 at 4:19
  • 2
    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.
    – Kendall Lister
    Dec 21, 2016 at 0:30
  • I wipe my research I did over a week ago
    – Ramhound
    Dec 21, 2016 at 3:08

2 Answers 2

Reset to default
8

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

Improve this answer
0

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.

Improve this answer
4
  • 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.
    – Kendall Lister
    Dec 7, 2016 at 4:04
  • 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
    Dec 7, 2016 at 4:51
  • answers.microsoft.com/en-us/msoffice/forum/…
    – Imager
    Dec 7, 2016 at 4:52
  • 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!
    – Kendall Lister
    Dec 7, 2016 at 4:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .