Imported dates have month first.

[lunker55]

I have imported some dates.
They are in the mm/dd/yy format.
Half of the cells, excel recognizes as dates(days under 12 used as months),
and the other dates as text(13 and above).
My dates are in column A: 10/06/24
Cell B1 has the formula: =LEN(A1)
Cell C1 has the formula:
=IF(LEN(A1)=5,A1,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))
This works for the text cells, but it doesn't reverse the date cells.
I think I got this formula from this newsgroup, but I can't find it.
Any help would be great.

Joe

 

[Dave Peterson]

If you're importing the data from a text file (.txt), then you should be able to
just file|open and the text to columns wizard will pop up and you can specify
the format of your date fields mdy or dmy or ymd or....

If you're importing from a .csv file, rename it to .txt and you'll get the
wizard.

 

[lunker55]

Thanks Dave

Joe

If you're importing the data from a text file (.txt), then you should be able to
just file|open and the text to columns wizard will pop up and you can specify
the format of your date fields mdy or dmy or ymd or....

If you're importing from a .csv file, rename it to .txt and you'll get the
wizard.