Follow this blog:: RSS; Email Alert

Microsoft Office

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

May 9, 2012, 7:37 AM PDT

Takeaway: 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?

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters.

About Susan Harkins

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.

Select multiple objects in a PowerPoint slide

A quick way to select an Excel data range

Comments

Add Your Opinion

Join the conversation!

Follow via:: RSS; Email Alert

See All Comments

Staff Picks
Top Rated
Most Recent
My Contacts

No messages found

7 Votes

+ -

Dates Systems

Tron16 9th May 2012

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.

View in thread

2 Votes

+ -

Agreed with Tron16

Rudi-S 10th May 2012

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.

View in thread

0 Votes

+ -

The Deeper Question

dogknees 5th Nov

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

View in thread

0 Votes

+ -

Changing calculation creates more problems

hfreezerburn 5th Nov

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?

View in thread

0 Votes

+ -

Moderator

That's because Feb 29, 1900 does not exist...

wizard57m-cnet 22nd Jul

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.

View in thread

Once logged in, adding contacts is simple. Just mouse over any member's photo or click any member's name then click the "Follow" button. You can easily manage your contacts within your account contacts page.

See all comments