Solved Excel date problem - to convert incorrect date to original

Microsoft Excel 2004 for mac
August 24, 2012 at 00:10:47
Specs: Mac OSX
Somehow the date on one of my old Excel files has turned from 1999-10-04 (i.e. October 4, 1999) to 4617-04-11. How can I change all the rest of the incorrect dates back to what they should be? This is the only date that I can confirm. And I have no idea what the rest of all the dates should be on this spreadsheet. TIA.

See More: Excel date problem - to convert incorrect date to original

Report •

August 24, 2012 at 11:46:56
I have no idea what the rest of all the dates should be on this spreadsheet

Then how do you purpose to change them, when you don't know what year they are supposed to be?

Somehow the date on one of my old Excel files

Any Macros running that may have in some way impacted the dates?

How is the data stored? As Text or as a Date?


Report •

August 24, 2012 at 12:08:29
✔ Best Answer
According to the DATEDIF function, 4/11/4617 is 956,029 days after 10/04/1999.

If the same factor was use to change the other dates, then this should convert them back:

1 - Enter 956029 in an empty cell.
2 - Ctrl-c to Copy
3 - Select your "weird" dates
4 - Edit...PasteSpecial...Subtract
5 - Format the 5 digit numbers as Date (or Custom) to format them to fit your needs.

If you don't think that the same factor was used to convert all of the dates, and you have no idea what the original dates were, then I don't think there's any way to fix your spreadsheet.

Logic dictates that you have to know 2 out of 3 things:

1 - The converted date (known)
2 - The orignal date (known for the example you supplied)
3 - The conversion factor (determined by the example you provided)

If you don't know any of the other original dates and you don't know if the same conversion factor was applied to all dates, then I think you are in deep doo-doo. (That's a technical term)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 25, 2012 at 14:23:25
There are a total of more than a thousand entries of dates under one column. Some of the earlier ones were entered (formatted) as text "yymmdd" (October, 27, 1993 was entered as 931027). Later on they were entered (formatted) as date "yymmdd" which would have looked the same as the earlier entries. For more recent years, I have left the format the same as date, except for showing as "yyyy-mm-dd". So they would look like 1993-10-27. Now most of the early entries are looking like some unknown number.
I can only confirm a few dates among all entries to be whatever they should be. But when I adjust to the different in days, it will only work around that date. It won't match when I approach to another or the next confirm date. So it's impossible to know when to use a different number.
I've tried everything I can think of. I think I'm in deep doo-doo and I'm just not going to try to fix them.
Thanks for all the help and suggestions from everyone.

Report •

Related Solutions

August 25, 2012 at 15:13:21
Best as I can figure, it looks like your dates which were formatted as Text
got converted to a Date format.

If you take 991004 ( Oct 4, 1999) and covert it to a date you get 4613-04-10.


The four year difference has to do with how your operating system, a Mac, keeps track of time.

Report •

Ask Question