- Follow via:
- RSS
- Email Alert
Question
0
Votes
Numeric text string to excel date
How do I convert the following text string "040802" into a date field in Excel "08/02/04"? Thanks
Tags:
software
14th Sep 2007
Answers (1)
0
Votes
Here is the solution
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.
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.
18th Sep 2007
