CODEWITHSUNDEEP
google-sheetstimezone
Zealix_Alpha
Zealix_Alpha

Reputation: 3

Convert string date from UTC to AEST

I'm importing submissions from a DUDA contact form into google sheets but the time stamp is coming across as a string in the wrong time zone (2020-11-16 23:26:29 UTC). As I can't change the form settings to convert the time zone automatically, is there a formula that I can use to do this every time a new submission is saved to the spreadsheet?

Upvotes: 0

Views: 374

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

It's not clear from your post where (i.e., in which sheet or column) your incoming data is. However, it is best practice never to add anything to a form-intake sheet. Rather, you should do manipulations in another sheet (even if you wind up hiding that sheet from view afterward), and then reference that conversion sheet elsewhere.

That said, supposing that your form-intake sheet is named "Form Reponses 1" and that your UTC dates are coming in as text into A2:A, you can try this formula in cell 1 of an otherwise empty column in another sheet:

=ArrayFormula({"Header"; IF('Form Reponses 1'!A2:A="",, DATEVALUE(REGEXEXTRACT('Form Reponses 1'!A2:A,"^(.+) ")) + TIMEVALUE(REGEXEXTRACT('Form Reponses 1'!A2:A," (.+) ")) + (10/24) )})

You can replace "Header" with a more meaningful header.

The results will likely come across as numbers in the 40,000 range with an extended decimal amount. This is the raw date/time format. Just set Format > Number for the column to whatever data/time format you prefer.

Upvotes: 1

Related Questions