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

or:

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

CodePudding user response：

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.

CodePudding user response：

Step 01

Use this formula

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

Step 02 - Format

Result