Is there any global time/date format parsable by Google Sheets etc?

user_13495433 0
Viewed 840

Is there any global timestamp format which can be automatically parsed and understood by most major spreadsheet software? (But especially Google Sheets.)

For example, consider the timestamp "2020-05-08T02:53:03+00:00". If I try to paste this into the spreadsheet as:

1) "2020-05-08T02:53:03+00:00" : not parsed by any major spreadsheet software
2) "2020-05-08T02:53:03Z"      : not parsed by any major spreadsheet software
3) "20200508T025303Z"          : not parsed by any major spreadsheet software
4) "2020-05-08 02:53:03 GMT"   : not parsed by any major spreadsheet software
5) "2020-05-08 02:53:03"       : parses; but only correct if user is in GMT/UTC

Please note that I am not the user, I cannot autodetect the user's timezone, and I cannot demand that they reconfigure their spreadsheet software to a particular timezone. All I can do is give them a string similar to the above -- which they expect to paste into their spreadsheet and it "just works". Anywhere in the world.

Format must be human-readable (no epoch times or Excel serials). User-side scripting is only acceptable if Grandpa can handle it. Grandpa just learned last week how to copy/paste (and still hasn't forgiven you for it). He uses Excel and will hurl a shoe at you if you say "Google Sheets". Or "script". Or "menu". Or "Obama".

Details: I'm writing a script which presents a table full of timestamps to the user (by web, email, etc). Some users will then copy/paste the table into their spreadsheet software, and they will expect this software to automatically and correctly parse the timestamps so that they can be converted to the user's local timezone, can be used in spreadsheet math, etc. This of course requires that I give them a format where the timezone is either unnecessary or clearly specified. Like most coders, I can easily convert timestamps to any string format.

Rant: The standard ISO 8601 formats (examples 1-3 above) were created specifically for this purpose. So I was rather shocked to find that not even the datevalue() function in Google Sheets can parse them. I mean, Google can't parse ISO 8601? Google?? This is so unbelievable that I require independent confirmation. Because I must be missing something. There must be some global timestamp format that Google can parse.

I mean, come on. This is 2020. And it's Google.

iso8601 google-sheets timestamp parsing
1 Answers

With Google Apps Script you can parse dates with formatDate(date, timeZone, format) like the following:

function myFunction() {
  var date = new Date('2020-05-08T02:53:03+00:00');

  var formattedDate = Utilities.formatDate(date, "GMT", "yyyy-MM-dd HH:mm:ss");

  Logger.log(formattedDate);
}

And you can also get the user's time zone with getScriptTimeZone().

With this you can make the user use this script on Sheets and with a trigger onOpen() or onEdit() reformat all the dates to their zone

user_89962110
Related