CODEWITHSUNDEEP
exceldate
Kay
Kay

Reputation: 2854

Convert date as text "YYYY:DD:MM hh:mm:ss" to real date

How can I convert a date given as text "YYYY:DD:MM hh:mm:ss" to a date with format YYYY.MM.DD hh:mm:ss?

Upvotes: 0

Views: 8376

Answers (2)

Dave
Dave

Reputation: 1

I just had the same problem and my file has 400,000+ rows with text in this format : yyyy-mm-dd hh:mm:ss. The custom date format did not work at first, but you can use =VALUE(text) and then apply the custom cell format of your choice. Somehow Excel knows your text is a date with an equivalent number butwill not let you change the format otherwise.

Upvotes: 0

user4039065
user4039065

Reputation:

The worksheet formula solution would involve the LEFT function, MID function and RIGHT function.

=DATE(LEFT(A1, 4), MID(A1, 9, 2), MID(A1, 6, 2))+TIME(MID(A1, 12, 2), MID(A1, 15, 2), RIGHT(A1, 2))

        Date text parsing

Format the cell (Ctrl+1) with a Numbers ► Custom ► Type: of yyyy.mm.dd hh:mm:ss.

Those functions work exactly the same way in VBA. The DATE function and TIME function would be DateSerial and TimeSerial.

Upvotes: 3

Related Questions