Not sure where I am going wrong here, but I am trying to convert times in a Google Sheet from 12-hour format, e.g. 02:15:00 pm, to 24-hour format 14:15:00. I have tried several different formulas and/or selected the format from Googles formatting time options. Numerous sites provide this simple formula in Column D =TEXT(C4,"[hh]:mm:ss") but that won't convert.

Please see the sample sheet:

CodePudding user response：

Use this formula

=ArrayFormula(IF(A3:A="",,
 TIMEVALUE(SUBSTITUTE(REGEXEXTRACT(A3:A," (. )"),".",""))))

Explanation

• REGEXEXTRACT the time and SUBSTITUTE the dots "." with nothing ".".
• set (time_string) of TIMEVALUE function to the output of SUBSTITUTE.
• ArrayFormula(IF(A3:A="",, to calculate only when the cells in A3:A is not empty