FM date vs Excel date difference

[djlane]

I have a calculation field which combines order number and order date for a price lookup.

I was have a problem with it so I placed the combined field on a layout to see the contents.

The order date is May 17th 2006, which results in a value of 732448 in my combined field.

But when I enter 732448 into Excel and format it as a date it returns the date May 16th 3905.

Why is that ??

[comment]

It's because Excel uses a different date system. In Filemaker, day 1 is Jan 1, 0001. In Excel, ... well it depends. There are actually 2 date systems, so day 1 is either Jan 1, 1900 or Jan 1, 1904 - depending on your platform and settings. The default is 1900 for Windows and 1904 for Macintosh, but you can reverse that on any platform.

A further problem, which I believe was never corrected in an effort to maintain backwards compatibility, is that Excel incorrectly assumes that 1900 was a leap year.

In practical terms, this means there's a difference of 693,595 days between Filemaker and Excel (1900 system). So if you just subtract 693,595 from your Filemaker date, you should be fine (except in the first 2 months of the year 1900).

[djlane]

Thanks "comment".

So, in exporting data to Excel, I should export the text string rather than the date serial number, or else remember to add that giant number..

[comment]

Basically yes, except:

1. You need to SUBTRACT "that giant number", not add it - otherwise you will be shot another 1,900 years into the future.

2. There are plenty things that can wrong with a text string as well. Is "5/1/2006" May 1, or is it January 5? It depends on your OS settings and also on the settings of your Filemaker file.

[xochi]

Beware -- Excel has a nasty "feature". It can use either a 1900-based date system or a 1904-based date system, depending on whether the file was created on Windows or Mac.

I'm not sure about the latest versions of Excel, but older ones (such as "Office X" for mac), are totally buggy -- open two spreadsheets (one using 1900-based dates, the other using 1904-based dates) and if you copy & paste between the two spreadsheets, Excel is not smart enough to get the dates right.

Since a 4 year offset for many things (like a birthdate) is within the range of normal, this bug can be very difficult to catch.

Watch out!

[djlane]

Thanks guys, all clear now