Reformat number to proper date using existing data

I need help converting numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.

I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.

The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.

Any help is greatly appreciated.

Originally Posted by :) Sixthsense :)

Or

Using Formula

In A1 Cell
100875

In B1 Cell
=TEXT(TEXT(A1,"00-00-00"),"DD/MM/YYYY")

I agree with SixthSense, as this will give you dates instead of text. However, being dates in the American format, you would use =TEXT(TEXT(A1,"00-00-00"),"MM/DD/YYYY")

Regards, David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".