Questions

Numeric text string to excel date

+
0 Votes
Locked

Numeric text string to excel date

tommy.hutchins
How do I convert the following text string "040802" into a date field in Excel "08/02/04"? Thanks
  • +
    0 Votes
    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.

  • +
    0 Votes
    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.