Excel and Filemaker calculates the difference between two dates differently. I always thought that when you subtract two date from one another, the result was the number of full days between the dates.

For example:  Calculate the difference between 1/15/19 – 1/9/19.

There are 5 full days between these dates (10th, 11th, 12th, 13th, and 14th). However, FM and Excel calculations do not result in 5, and they don’t even agree with each other!

In Excel, Difference = 7

In FM, Difference = 6

It seems that when you subtract two dates in Excel the result includes the starting and ending dates and therefore it is not the number full days between the dates. In FM the result seems to include either the starting or ending date (not both), and therefore it also is not the number of full days between the dates.

What gives?

What formula are you using in Excel to calculate the difference? 

Filemaker simply converts both dates to serial numbers, starting with the date 1/1/0001 being the first day, then subtracts the two numbers. AFAIK, Excel's DATEDIF() function works in a similar fashion and returns the same result. I don't have Excel installed, but I get the same result in LibreOffice by just subtracting the two dates and formatting the result as number.

 

58 minutes ago, john9210 said:

There are 5 full days between these dates

The logic that Filemaker (and, I believe, Excel as well as most of us) applies here is this:

• the difference between today and today is 0 elapsed days;

• the difference between yesterday and today is 1 elapsed day;

• the difference between last Sunday and this Sunday is 7 elapsed days; 

• etc.

Your logic would have the difference between today and today as -1 elapsed days.

 

 

 

Edited July 21, 20196 yr by comment