Problem Importing Dates from Notepad into Excel

Question

When I import dates from Notepad (.txt) into Excel, they're importing in a YYYYMMDD format, as opposed to MM/DD/YY. Any ideas?

Answer 1

When I import dates from Notepad (.txt) into Excel, they're importing in a YYYYMMDD format, as opposed to MM/DD/YY. Any ideas?

First you really should post this to the Excel forum. But your problem is that the dates are probably in the YYYMMDD format in the text file. Excel (or Access) will not know that these are dates ans will interpret them as either numerical or text data. AFTER you import you will need to parse the pieces or and convert to an actual date value.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

For what it's worth, I ran that test myself because it was an interesting question.

I confirm that Excel imported a text file with values as YYYYMMDD as text cells.

And I confimed that Excel imported another text file with values as MM/DD/YYYY as date cells.

May I recommend to the user to add this formula to the next column and roll that formula down?

=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))

where A2 is the first cell with the YYYYMMDD value.

To study how this works, please lookup the DATE() and MID() functions.

Answer 2

When I import dates from Notepad (.txt) into Excel, they're importing in a YYYYMMDD format, as opposed to MM/DD/YY. Any ideas?

First you really should post this to the Excel forum. But your problem is that the dates are probably in the YYYMMDD format in the text file. Excel (or Access) will not know that these are dates ans will interpret them as either numerical or text data. AFTER you import you will need to parse the pieces or and convert to an actual date value.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries