john9210 Posted July 21, 2019 Posted July 21, 2019 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?
comment Posted July 21, 2019 Posted July 21, 2019 (edited) 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, 2019 by comment
Recommended Posts
This topic is 2204 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 accountSign in
Already have an account? Sign in here.
Sign In Now