Solved

Excel dates off by 4 years

Posted on 2011-02-21
6
256 Views
Last Modified: 2012-05-11
I have been sent an Excel file which contains some dates in 2 columns.  On visual inspection, the date values are correct in the sense that someone's date of birth matches a record we already have for them.

However, when I process the spreadsheet (using an ASP script but this also happens with a simple copy and paste to a new spreadsheet), the dates are all changed by 4 years and 1 day smaller.

The format of the column shows the expected DD/MM/YYYY (British English) format and locale - the file originated in the Republic of Ireland.

I'm at a loss as to how this can happen and how I can correct it for the future.
0
Comment
Question by:OwenParry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 total points
ID: 34941864
Hello,

It would help if you stated your Excel version.

In Excel 2003 and younger, go Tools > Options > Calculation tab > uncheck "1904 date system"

In Excel 2007 and later go Office button/File > (Excel) Options > Advanced > scroll down to "When calculating this workbook" and uncheck "use 1904 date system"

cheers, teylyn
0
 

Author Closing Comment

by:OwenParry
ID: 34941900
Many thanks - just the thing
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34941915
OwenParry,

The chances are the original dates were entered using Apple's base date which was 1904 (the first day that is supported is January 1, 1904) rather than Excel's normal base date of 1900 (the first day that is supported is January 1, 1900) - thus the difference of 4 years.

Ref: http://support.microsoft.com/kb/180162

Patrick
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 34941924
ps. The difference of 4 years and a day is accounted for by leap years - or absence of them - plus the different datal systems.
0
 

Author Comment

by:OwenParry
ID: 34941936
Thanks - we don't support Macs here so none of us had come across the difference.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34941952
>Thanks - we don't support Macs here so none of us had come across the difference.

I understand that however Excel can work with either datal system as teylyn has indicated in comment ID:34941864.

Patrick
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question