datetime timezone order google sheets yyyy-mm-dd hh:mm:ss GMT to dd-mm-yyyy hh:mm:ss GMT

uservLx11 0
Viewed 17

I have a Google sheet that needs timestamp in one column as is '2022-09-11 00:13:50 GMT' and in another column I need to convert to '11-09-2022 00:13:50 GMT'

I have tried:

  1. copying cells into new column and reformatting but it isn't working
  2. =datetime(A1,"dd/mm/yyyy hh:mm:ss")

  3. =TEXT(A1,"dd/mm/yyyy hh:mm:ss")

  4. =date(A1,"dd/mm/yyyy hh:mm:ss")

  5. =Timestamp(A2,"dd/mm/yyyy hh:mm:ss")

Note the format column A is in is how it imports from another fixed sheet

I probably just too tired and missing the most obvious thing but I can't figure it out

https://docs.google.com/spreadsheets/d/1alTL1z1ZXIr8rjF6hFHmJuD81OFK7TmqFdnC8ILIPwI/edit?usp=sharing

timestamp time date datetime google-sheets
3 Answers

try:

=ARRAYFORMULA(REGEXREPLACE(A1:A, "(\d{4})-(\d+)-(\d+) (.+)", "$3/$2/$1 $4"))

enter image description here

or:

=ARRAYFORMULA(IF(A1:A="",,REGEXREPLACE(A1:A, 
 "(\d{4})-(\d+)-(\d+) (.+) GMT", "$3/$2/$1 $4")*1))

enter image description here

player00

Put this formula in cell D1:

=arrayformula( iferror( 1 / value( regexreplace(A1:A, " GMT", "") ) ^ -1 ) )

Then format column D through Format > Number > Custom date and time.

doubleunary0

Step 01

Use this formula

=ArrayFormula(IF(A1:A="",,DATEVALUE(REGEXEXTRACT(A1:A,  "(.+?) "))+
                          TIMEVALUE(REGEXEXTRACT(A1:A, " (.+?) "))))

enter image description here

Step 02 - Format

enter image description here

Result

enter image description here

Osm0
Related