Regarding @SteveJG's point, most spreadsheet software uses the Microsoft format, which is a fractional form where the integer part is days since Dec 30, 1899 (ref Why 30th December 1899?) and the fractional part is multiplied by 86400 to get a particular second in a day (excepting leap seconds...). Therefore, I expect the "right" format will likely be this one support for Unix timestamps would be appreciated (but I'm not going to hold my breath).
Also, regarding timezones:
1 Like
As a stopgap, I suggest just formatting the datetime according to
which would be accepted by Google Sheets and leaving it to the app developer to follow the other recommendations at the bottom of Experiments with Logging DateTimes to Google Sheets
An AI2 app developer spanning time zones with their app would need to deal with their time zone issues on their own, while a new coder would still have a safety net under them if they send an Instant to Sheets.
I do think ISO 8601 tends to be a pretty good option (especially if you go the extra step to include the source timezone). In most cases, apps will likely not leave the original time zone as most of our users are students, but either UTC or ISO 8601 + timezone should cover most use cases, and for more advanced folks hopefully they're willing to take the dive into the time zone quagmire.
Whatever datetime format you use, it would be wise to see if Sheets can parse it using the =VALUE() function.
Here is the failure I got when i tried to send a 'z' format suffix:
(The VALUE() works when I don't send the EST.)
So according to the Google Sheets documentation, VALUE requires that the input be a datetime format that Google Sheets understands (a bit of a tautology, but anyway...).
According to this document, it looks like there isn't a way to assert time zones with the Google functionality, a bit surprising if true.
If this were a properly formatted ISO 8601 date-time, you'd want to be able to parse something like 2022-11-08T04:20:38-05:00
I tried the ISO 8601 X X X date format, from SimpleDateFormat (Java Platform SE 7 )
but it is not recognized by Sheets:
So far, there is no way to encode the time zone into the datetime that Sheets will recognize.
I don't know how particular sheets is on this front, but there shouldn't be a space between the time and the timezone.
Eh. I've tried a bunch of different things, but it looks like Sheets just will not accept a time zone per the ISO 8601 guidelines...
Relevant XKCD:
Removing the space did not improve Sheet's recognition capabilities.
In baseball, the solution would be to punt bunt:
What one uses for time depends on what one is gonna' do with it. Unix is fuzzy, UTC is simple.
The challenge here is that with something like Sheets as a backend, there is the potential that it is shown to someone outside of the context of the app. Neither Unix timestamps or UTC timestamps are particularly useful in that regard since you want to show the information as it is relevant to the viewer.
If it is 12:00 utc it means that the local time is + or - a number, usually an integer in arrears or ahead of the local time. All anyone needs is to know that UTC is what is posted to determine what is being talked about. UTC is a time standard that forms the base of time zones worldwide and is not used as a time zone in any country. Time zones are defined by their offsets from UTC.
UTC now (16:17 CST) is 22:17 GMT (London is 6 hours ahead of Dallas. or it is now 17:17 EST in Boston, and London is 5 hours ahead).It is 06:17 in Hong Kong (but be careful, it is already Wednesday morning but is still 22:17 in London and it is dark and still Tuesday!
In any case, please do not use AOE
mentioned as the Appathon time deadline. AOE is ambiguous and not a generally recognized time zone. 
Yes, it is, but maybe it is there, to give a chance to an Appathon Participant to submit there project work, who is belonging to time zone represents the last place on Earth - Howland Island.
OR providing something-more/extra time to 
I have another small experiment, requiring viewers from across the globe, to tell me if and how Google presents datetime values to people in different time zones.
Please go to this URL to view the 'Testing' sheet at this URL:
Take a screen shot including row 1 columns D and E (at least), and post it here.
I am hoping to see if Google adjusts its datetime values and formats to local time.
Thank you.
So my answer is that Google shows the datetime according to the format of the cell, and according to the sheet's settings of time zone, regardless of the time zone of the viewer.
A post was merged into an existing topic: When I send data with spreadsheet write cell, I get the error shown in the picture. why? What can I do? thanks...