CODEWITHSUNDEEP
excelcsv
cristi _b
cristi _b

Reputation: 1813

FIxing MS Excel date time format

A reporting service generates a csv file and certain columns (oddly enough) have mixed date/time format , some rows contain datetime expressed as m/d/y, others as d.m.y

When applying =TYPE() it will either return 1 or 2 (Excel will recognize either a text or a number (the Excel timestamp))

How can I convert any kind of wrong date-time format into a "normal" format that can be used and ensure some consistency of data?

I am thinking of 2 solutions at this moment :

Thanks

Upvotes: 1

Views: 1062

Answers (2)

SeanC
SeanC

Reputation: 15923

If you can't get the data changed then you may have to resort to another column that translates the dates: (assumes date you want to change is in A1)

=IF(ISERR(DATEVALUE(A1)),DATE(VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,4))),VALUE(MID(A1,FIND(".",A1)+1,2)),VALUE(LEFT(A1,FIND(".",A1)-1))),DATEVALUE(A1))

it tests to see if it can read the text as a date, if it fails, then it will chop up the string, and convert it to a date, else it will attempt to read the date directly. Either way, it should convert it to a date you can use

Upvotes: 1

Tim Pietzcker
Tim Pietzcker

Reputation: 336468

Certainly your second option is the way to go in the medium-to-long term. But if you need a solution now, and if you have access to a text editor that supports Perl-compatible regular expressions (like Notepad++, UltraEdit, EditPad Pro etc.), you can use the following regex:

(^|,)([0-9]+)/([0-9]+)/([0-9]+)(?=,|$)

to search for all dates in the format m/d/y, surrounded by commas (or at the start/end of the line).

Replace that with

\1\3.\2.\4

and you'll get the dates in the format d.m.y.

Upvotes: 1

Related Questions