Excel Changing Dates when Copying Cells

Microsoft Excel 2007
May 13, 2009 at 11:11:17
Specs: Windows XP
We are GOING CRAZY! We have a column of dates in a HUGE Excel file, and when we copy of paste, paste special, values, Excel is automatically changing the year of the dates. HOW DO I GET EXCEL TO STOP DOING THIS? We've spent days trying to figure this out and are nearly resigned to defeat. Please help if you can.

See More: Excel Changing Dates when Copying Cells

Report •

May 13, 2009 at 11:55:17
Your not giving us a lot to work with here....

Give some before and after examples.
How do you have the "before you copy" date cells formatted?
How do you have the "after you copy" date cells formatted?



Report •

May 13, 2009 at 12:24:28
Thanks for responding. Here are the specifics:
Format of cell in original file: Date mm/dd/yyyy
Format of cell in new file to which date field is being copied: Date mm/dd/yyyy. What is happening is that Excel is changing the last digit of the year. So if the original date was 10/15/2006 the copied cell is 10/15/2002. What we have found is that year 2006 copies as 2002, 2007 copies as 2003, 2008 as 2004 (it's always 4 years off). What is even stranger is that the font color changes from black to green. We are LOST! Is the original file just corrupt?

Report •

May 13, 2009 at 12:41:51
Look at Tools...Options...Calculation tab.

Under Workbook Options, is the date system set the same way in both files?

Report •

Related Solutions

May 14, 2009 at 07:03:54
DerbyDad you are my HERO!!!! It had to do with "Use 1904 date system". It was checked on one file (the master one we were copying) and not checked on the new file. I feel like celebrating. THANK YOU DERBYDAD! Let me know if there's something I can do to return the favor.

Report •

May 14, 2009 at 08:55:05
re: Let me know if there's something I can do to return the favor.

Actually, there is.

I was able to answer your question based on the specifics posted in your Response # 2. Once you said that the difference was always 4 years, I went straight to taking a guess that it was 1900/1904 date system issue.

So the favor you can do is to send an email/text/IM/snail-mail to any one who will ever post a question to this forum and ask them to make sure they included enough detail in their question so that we can at least have clue as to how to help them.

We see so many questions that are lacking key pieces of information that there is no way to answer them. I don't mind asking for more details if it appears that the poster may not understand the subtleties involved in the solution (such as your question). However, I have to wonder about what the poster expects when they post things like "My computer's not working. Please help."

Here's an actual post to this forum:

Subject: Macros Missing Errors
Body Text: now what?

Would you like to take a shot at answering that one?

So, write that email for me and get it out there.

Thanks! <g>

Report •

Ask Question