• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Excel dates off by 4 years

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
OwenParry
Asked:
OwenParry
  • 3
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
OwenParryAuthor Commented:
Many thanks - just the thing
0
 
patrickabCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
patrickabCommented:
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
 
OwenParryAuthor Commented:
Thanks - we don't support Macs here so none of us had come across the difference.
0
 
patrickabCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now