none
Excel datetime - timezone RRS feed

  • Question

  • Hi all;

    There's a ton of documentation saying that Excel tracks the number of days since 1/1/1900 as a number for datetime. But nothing says anything about timezones.

    Is this against UTC? Or against some defined timezone? Or is it local time? So if I open a spreadsheet in Colorado and someone else opens it in Germany, then we'll see the same time even though those are different instants in time?

    ??? - thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, March 14, 2019 1:22 AM

Answers

  • The 'number' is integer days + time as a decimal fraction of a day relative to the computer clock. Your users in different timezones will normally see their own time. However if a datetime is saved in a workbook as a number in one timezone when opened in any other timezone users will see the original saved value not adjusted to their timezone.

    For working with timezones see

    http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

    A file's create/modify datetime stamps may or may not be timezone aware, too much to go into here but search the keywords earlier in this sentence.

    • Marked as answer by DavidThi808 Thursday, March 14, 2019 6:56 PM
    Thursday, March 14, 2019 12:02 PM
    Moderator
  • The datetime value is simply a number, your 2019-03-14T13:15:22 value is 43538.552337963. This value when formatted as a date time will display the same indefinitely in any timezone on any system, irrespective of whether its clock is linked to the local time zone. 

    If necessary identify the timezone separately, eg UCT-5, EST, EDT. The identifier could be included in the included in the numberformat

    • Marked as answer by DavidThi808 Thursday, March 14, 2019 6:56 PM
    Thursday, March 14, 2019 6:47 PM
    Moderator

All replies

  • The 'number' is integer days + time as a decimal fraction of a day relative to the computer clock. Your users in different timezones will normally see their own time. However if a datetime is saved in a workbook as a number in one timezone when opened in any other timezone users will see the original saved value not adjusted to their timezone.

    For working with timezones see

    http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

    A file's create/modify datetime stamps may or may not be timezone aware, too much to go into here but search the keywords earlier in this sentence.

    • Marked as answer by DavidThi808 Thursday, March 14, 2019 6:56 PM
    Thursday, March 14, 2019 12:02 PM
    Moderator
  • However if a datetime is saved in a workbook as a number in one timezone when opened in any other timezone users will see the original saved value not adjusted to their timezone.

    There's a bit of ambiguity in your statement and I want to make sure I have this right. I save it as 2019-03-14T13:15:22 in a spreadsheet in Colorado. I email that spreadsheet to someone in Germany. They open it and it displays 2019-03-14T13:15:22 to them too. So same spreadsheet, same underlying numbering the XLSX, displays the same in both locations. But obviously two different instants.

    Is that correct?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, March 14, 2019 1:09 PM
  • The datetime value is simply a number, your 2019-03-14T13:15:22 value is 43538.552337963. This value when formatted as a date time will display the same indefinitely in any timezone on any system, irrespective of whether its clock is linked to the local time zone. 

    If necessary identify the timezone separately, eg UCT-5, EST, EDT. The identifier could be included in the included in the numberformat

    • Marked as answer by DavidThi808 Thursday, March 14, 2019 6:56 PM
    Thursday, March 14, 2019 6:47 PM
    Moderator