Jump to content

FM date vs Excel date difference


This topic is 5680 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5680 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.