Google Sheets – Convert Datetime String to Datetime Value

google sheets

Let's say cells A1 and A2 have these values:

13 Jan, 02:21 am

12 Jan, 10:15 pm

And many more values like those below in that column.

What formula could I use so that the B column would look like this?

2020-01-12 22:15

I'm confused about DATE(), DATEVALUE(), TIMEVALUE(), etc.

In PHP it is so much easier: https://www.php.net/manual/en/function.strtotime.php

Best Answer

I could find no magic function in google sheet. However, you may use the following.

Before applying this function, you need to add year value like

change
"13 Jan, 02:21 am" // google will parse this as current year date as of 2021
to 
"13 Jan 2020, 02:21 am"

then

=index(split(A1,","),1)+index(split(A1,","),2) 
// split function will trigger automatic type match for each value

The above function is equivalent with

=datevalue("13 Jan 2020") + timevalue("02:21 am")

As cell value will be 43843.09792 as number, you need to change cell value format as DATETIME.

Google Sheet Example https://docs.google.com/spreadsheets/d/1nvvKiLyzZNdekx5-hDpDqzJ6T401G-mASqiKCZbUiQk/edit?usp=sharing

Related Topic