How to extract date and time in Google Sheets [closed]

Question

I'm trying to extract the time and date from column A.

In cell A1 for example is written: October 25, 2023 at 07:23PM

In cell A2 for example is written: October 26, 2023 at 01:01AM

I'm trying to extract the date and time as a formatted 'time / date' value, so that I can calculate the time difference between the two. Note, that the time needs to be formatted in a 24-hour format.

The formula I've tried is this here:

=TEXT(TIME(VALUE(REGEXEXTRACT(A1; "\d{1,2}"); VALUE(REGEXEXTRACT(A1; "(\d{2}:\d{2})([AP]M)")) + IF(REGEXMATCH(A1; "PM"); 12; 0)); "HH:mm")

I've also tried already just to extract just the time from it. But it displays then the time wrongly, as 06:28PM is not equal to 06:28 (which would be AM in a 24-hour time format)

=value(regexextract(A1; "\d{1,1}:\d{2}"))

Answer 1

That conversion should be quite straightforward when the spreadsheet locale is United States or another locale where the name of the tenth month is October. Use value() and split(), like this:

=value(join(" ", split(A1, " at ", false)))

Format the formula cell as Format > Number > Date time or a custom format of your preference.

To apply a format programmatically, use query(), like this:

=query( 
  value(join(" ", split(A1, " at ", false))), 
  "format Col1 'yyyy-MM-dd hh:mm' ", 0 
)

When the spreadsheet locale does not recognize October, use regexextract() and match(), like this:

=let( 
  t; to_text(A1); 
  year; regexextract(t; "\d{4}"); 
  month; match( 
    regexextract(t; "[A-z]{3}"); 
    hstack("Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"); 
    0 
  ); 
  day; regexextract(t; "\d+"); 
  hour; regexextract(t; "(\d+):") + 12 * regexmatch(t; "PM$"); 
  minute; regexextract(t; ":(\d+)"); 
  dateserial; value(join("-"; year; month; day)) + time(hour; minute; 0); 
  query( 
    dateserial; 
    "format Col1 'yyyy-MM-dd hh:mm' "; 0 
  )
)

See See Working with date and time values in Google Sheets, regexextract(), match(), value() and query().