Software optimize

Office challenge: Why does Excel change dates when I copy a sheet to a new workbook?

This week, test your Excel skills by identifying and remedying this troublesome date behavior.

Copying or moving a sheet to a new workbook is a simple task, usually. But, suppose a simple copy task ends with a call to you for help because the new workbook shifts each date value by four years and a day! For instance, the new workbook converts the original date 5/2/2012 to 5/1/2008. What's happening and can you find a happy ending for this user?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
hfreezerburn
hfreezerburn

I'm having this problem of copying dates out of a workbook from a 3rd party into my own company's workbook and it adding 4 years. However, if I try changing the setting on mine (a mac) to calculate from 1900 instead of 1904, it'll change all the other dates in my workbook (there are over 4000, so I'm not about to go in and manually fix those). Is there any solution other than manually changing either my dates or the 3rd party dates?

J.A.Edwards
J.A.Edwards

I dont know if it's still true, but Windows Excel had a different start day to Mac Excel, so copying from one "source" to another may cause this type of issue (it did "in the old days" :-).

jnmheijer
jnmheijer

Excel can not handle all leap years correctly: try date 29-02-1900

JohnOfStony
JohnOfStony

Because the USA and UK write dates differently - US = mm/dd/yyyy, UK = dd/mm/yyyy, it's not sensible to use a number for the month as 3/5/2012 will be read as March 5th in the USA and 3rd May in the UK. If the month is written as its 3 letter abbreviation, then there's no confusion. Whether it's written May 3, 2012 or 3 May 2012, it's unambiguous. I propose that everyone stops using a number for the month. Does anyone have any objections? Incidentally, can anyone explain why the USA uses the format it does? It makes sense to go from the smallest time unit to the largest or from the largest to the smallest (as we all do when writing times) but month day year is just weird! (I'm British, by the way) I prefer the Year Month Day format then sticking the time after the date makes logical sense as the entire time string is in the order largest first down to smallest last. After all, that's how we write numbers with the largest (highest power of 10) digit on the left and the smallest (lowest power of 10) digit on the right.

paul
paul

As Tron says its the 1900-1904 date system. Each system creates a serial number based on either 1st Jan 1900 or 1st Jan 1904 being the days elapsed from then to the specified date, the difference between these numbers is 1462 days or 4 years and 1 day including 1 leap day. Every workbook can be set independently from Tools>>Options>>Preferences. Fatuous question, anyone know what happens in any 100th year that is not divisible evenly by 400 when there are no leap years, next one being 2100

Dukkman145
Dukkman145

I am trying to create a conditional format on a formula that has multiple constants added together =100+2000+225. I can get a conditional format if it is a Formula but I need a different formatting for formulas of this nature.

Rudi-S
Rudi-S

Each workbook can toggle between the 1904 date system, often used to calculate on negative time. The workbook copied to was probably using that system. Go to File Options, and deselect the tick mark next to Use 1904 date system.

Tron16
Tron16

Looks like new workbook is using a different date system than your old workbook. There is an option to use the 1904 or 1900 date system in excel. Make sure the date systems match up.

wizard57m-cnet
wizard57m-cnet

leap year doesn't happen on all century changes, only every 4th, so to find the previous leap year on a new century you have to go back to 1600. There is a leap year every year whose number is perfectly divisible by four - except for years which are both divisible by 100 and not divisible by 400. The second part of the rule effects century years. For example; the century years 1600 and 2000 are leap years, but the century years 1700, 1800, and 1900 are not. This means that three times out of every four hundred years there are eight years between leap years.

ssharkins
ssharkins

May 20, 2012 - May twentieth, two-thousand twelve. We don't say 20 March... or even the twentieth day of March, two-thousand twelve. So, I guess that explains why we prefer this format.

david.hanshumaker
david.hanshumaker

Excel seems to handle leap years correctly. If you subtract 2-1-2100 from 3-1-2100 it gives 28, but 3-1-2400 minus 2-1-2400 = 29.

dogknees
dogknees

Why do you say March 20 rather than 20th of March? When did the USA switch it around and why?