CODEWITHSUNDEEP
exceldateexcel-formula
Armitage2k
Armitage2k

Reputation: 1274

Excel - convert incorrect date format

I have a total of 3 dates which all come from outside my system and are formatted as mm/dd/yy which excel seems to have troubles picking-up correctly, occasionally mixing up the dd and mm values (eg. 7/1/17 can be the first of July 2017, but is at times captured as the 7th of January 2017).

I am looking for a way to format all dates as yyyy-mm-dd to avoid any mistakes at all, but am not having much success in reformatting with date() or datevalue().

Any advice on how to tackle this?

Thanks, A2k

Upvotes: 0

Views: 478

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

[done in Google sheets, but Excel should be similar] Suppose the funky date sits in cell A2. I did the following (several steps could be taken out and embedded in other formulas, but I wanted it to be clear). In B2 I placed the position of the first slash (might be 2 or 3) as =SEARCH("/",A2). In C2 I placed the position of the second slash as =search("/",A2,B2+1). In D2 I can get the year as =2000+right(A2,2) (I am assuming year is always in 2000s, but this code actually handles if a2 ends in /2017). In E2 I have the month as =mid(A2,B2+1,C2-B2-1). The day is in F2 as =left(A2,B2-1) Finally F2 gets =date(D2,E2,F2) and you can choose a date format from the cell formats to format it as yyyy-mm-dd as desired.

Upvotes: 1

Related Questions