Question Locked

Numeric text string to excel date

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How do I convert the following text string "040802" into a date field in Excel "08/02/04"? Thanks

2 replies

by apiy

Select the column containing the original text and set its format to text
In a new column put following formula:
=date(20 & left(a1,2),mid(a1,3,2),right(a1,2))
replace a1 with actual cell reference in your work sheet. If you have date prior to year 2000 then replace 20 with 19 for those dates
change the format of the new column to your desired format i.e mm/dd/yy
last step is to copy the new column and paste special as values

Hope this helps let me know your email and I can send you the screen shots.

This discussion is closed to new replies.