Cell date value, wrong numeric value

Question

I received an excel file from a client where the displayed date is different from its numeric value. Where the Australian format date "03/08/2010" = 38931 , which is wrong. It should be 40393. Its off by four years! Is it a settings in the excel file itself ?

Thanks for the reply.

Answer 1

Check the calendar. If the date is out by 4 years, it suggests that there's a discrepancy between the Windows 1900 calendar and the Mac 1904 calendar. To check this, load the workbook in Excel and go to Tools menu (or Edit menu in Excel 2001 for Mac), click Options or Preferences. Then, click the Calculation tab. There should be a 1904 Date System check box to indicate which calendar the workbook is using.

EDIT

3rd August 2010 is 40393 when the Windows1900 calendar is used, and 38931 when the Mac1904 calendar is used

You can read more about the Excel calendar system here.