Text File to Excel - Won't Format as Date

[js0873]

I have a text file that's opened with excel. Column A has dates, but Excel formats it as General. The date in A1 is 6242021, representing today. But if I try to convert that to a date format Excel shows all ######. I realize that Excel doesn't think that number is a date, but what's the best way to convert it to a date?

[MrShorty]

I would use text manipulation functions to parse out the month day year values, then use the date function.
=DATE(RIGHT(text,4),LEFT(text,1),MID(text,2,2)) or similar.

[js0873]

Interesting. Thanks. I was headed that way and thought there’s likely something easier I might be missing, but that’s totally doable.

[MrShorty]

If you have any control or influence over the text file, if you can ask/insist/demand that dates in your text file be sent as mm/dd/yyyy (or whatever matches your OS's system settings), then Excel can automatically recognize the date when it opens/imports the text file. But I cannot know if it is easier for you to request/demand a change in the text file or use a DATE() function in what you are given.

[js0873]

Actually the raw data text file has that field in mmddyyyy format. But if you just right click point to the text file and say open with Excel, the default import format is apparently General for all fields. This is something a clerk with be doing often, and I don't want her having to go to Excel, Open, point to the location, then go through the import wizard 3 times and finally say import as Text, not General. I doubt there's any way to specify that a default "Open with Excel" of a text file should use Text vs. General. But maybe there is. But since I'm doing an edit macro it's easy enough to format that as a date.