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


This feels hacky but suffices in the short run for my purposes:

=date("2020","01", left(A1,2))+TIMEVALUE(right(A1,8))

(And then Format > Number > More Formats > Custom number format > "yyyy-mm-dd hh:mm".)

But hardcoding the year and month feels wrong.


With Text date in A1, in another cell enter:

=DATEVALUE(left(A1,find(",",A1)-1) & " 2020")+timevalue(mid(A1,find(",",A1)+2,99))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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