CODEWITHSUNDEEP
excelvbaexcel-formula
Mohammad Imran
Mohammad Imran

Reputation: 61

Text String to Date Time Conversion in Excel

I am kind of struggling with dates in Excel. So, here is the case:

I have dates in Column AP in text format as I imported the same from a CSV. All the dates are suppose be DD-MM-YYYY HH:MM:SS.

Using the formula =DATEVALUE(TEXT(AP2,"DD-MM-YYYY")) + TIMEVALUE (TEXT(AP2,"HH:MM:SS")) I have been able to convert most of the dates. However, for certain dates like "13-06-2018 00:08:42" or "15-06-2018 00:10:11", the mentioned formula doesn't work. I am getting #VALUE!

Need help.. Thanks..

Upvotes: 0

Views: 107

Answers (1)

xidgel
xidgel

Reputation: 3145

Since you're getting #VALUE! when the first field is greater than 12, I surmise that your formula is mixing up the month and date fields. One brute-force way to get the dates is:

=DATE(MID(AP2,7,4),MID(AP2,4,2),MID(AP2,1,2))+TIME(MID(AP2,12,2),MID(AP2,15,2),MID(AP2,18,2))

Hope that helps

Upvotes: 1

Related Questions