CODEWITHSUNDEEP
excelexcel-formulavba
jeroen
jeroen

Reputation: 51

Convert cell value to DateTime

I have a row of cells in Excel filled with datetimes in the following messy format: July 13, 2016 at 12:10AM. I want to convert this to 2016/07/13 12:10 ('yyyy/mm/dd hh:mm' in the 24h format). Can I do this using a formula or should I write a vba code for this?

Upvotes: 0

Views: 264

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Two issues:

First you must remove the at

Second you need a space between the time and the AM or PM

The follow formula will turn it into a date/time:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"at",""),"AM"," AM"),"PM"," PM")

Then you can format the cell with the custom format:

yyyy/mm/dd hh:mm

enter image description here

Upvotes: 1

Related Questions