Home > Blockchain >  Google Sheet convert time format to another
Google Sheet convert time format to another

Time:09-15

Whenever I extract raw data from this specific tool I am getting the date format of

Fri Sep  2 10:29:50 2022
Fri Sep  9 10:31:01 2022
Mon Sep 12 10:32:28 2022

and I am having a hard time converting it to this kind of format in Google Sheet

9/2/2022 10:29
9/9/2022 10:31
9/12/2022 10:32

Already tried using different of formating but still can't convert it to the above format.

CodePudding user response:

use:

=ARRAYFORMULA(IF(A1:A5="",,REGEXREPLACE(TRIM(A1:A5), 
 "(.*) (.*) (.*) (.*) (.*)", "$2/$3/$5 $4")*1))

enter image description here

then do:

enter image description here

CodePudding user response:

Date value

=ArrayFormula(IF(A2:A="",,
 DATEVALUE(REGEXEXTRACT(TRIM(A2:A), "(. \d{1,2}) \d{1,2}:")&" "&REGEXEXTRACT(A2:A, " (....\z)")) 
 TIMEVALUE(REGEXEXTRACT(TRIM(A2:A), " (\d{1,2}:\d{1,2}:\d{1,2}) "))))

enter image description here

Custom date and time formats

enter image description here

Custom number formats

enter image description here

CodePudding user response:

Use custom time and date settings in format tab

  • Related