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}"))

  • Please add more details: the spreadsheet locale (country) , the data type of each value (text, number, boolean, date...), the results of the formulas that you tried, etc.
    – Rubén
    Oct 26 at 11:57
  • By the way, we have a lot of questions about handling dates in Google Sheets.
    – Rubén
    Oct 26 at 12:01

1 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:

  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:

  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"); 
  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); 
    "format Col1 'yyyy-MM-dd hh:mm' "; 0 

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


Not the answer you're looking for? Browse other questions tagged or ask your own question.