CODEWITHSUNDEEP
excel
Tilo
Tilo

Reputation: 429

Excel: Need to retrieve Date in " yyyy/mm/dd hh:mm" format from string having date and time

I have column in excel which has values like "12:00 AM CDT on June 01, 2017" and I need to convert it into format 2017/06/01 00:00

Any lead will be appreciated

Upvotes: 1

Views: 139

Answers (1)

Kresimir L.
Kresimir L.

Reputation: 2441

Easiest way to do this, if CDT on part of text is constant, is to use this formula in cell B1(in case your dates start in cell A1) and drag it to the bottom of dates. In B1 enter =SUBSTITUTE(A1,"CDT on ",""), After you substituted all text via formula, then you copy/PasteSpecial value formulas in same range. Then select whole column with clean dates, go to Data tab==>Text to Columns==>choose delimited type==>press next until last question==>there select date type and click finish.

Upvotes: 1

Related Questions