14 Replies Latest reply on Nov 3, 2014 12:58 PM by alquimby

# Inaccurate Elapsed Time Calc - Help!

Hi All,

I have been using a calculation I found on technet for quite a while that calculates the years, months and days between two given dates.

It had been working great until today. I had the dates 02/01/2015 amd 12/02/2015 in my date_start and date_end fields respectively. This calculation returned 9 months and 30 days. This is not accurate! It should have read 10 months and 2 days. How do I fix it? Here is the calc I used in the link below.

Thank you all.

Stephen

• ###### 1. Re: Inaccurate Elapsed Time Calc - Help!

http://www.fmdiff.com/fm/agecalc.html

or brian dunnings custom function repository

• ###### 2. Re: Inaccurate Elapsed Time Calc - Help!

Update: So the first link you recommended was working beautifully.  However, when I entered 02/01/2015 to 11/30/2015, it returned 9 months and 1 day.  It is actually 9 months and 30 days.

• ###### 3. Re: Inaccurate Elapsed Time Calc - Help!

Could you clarify you expectations? Calculating this is surprising tough, in part because it relies on some arbitrary decisions.

I'm not a big fan of the FM Help's calc, but it isn't actually wrong in this case. For example, why isn't 9 months and 29 days correct? 2/1/15 to 3/2/2015 is 29 days. 3/2/15 to 12/2/15 is 9 months. That's 9M 29D. You could also count it at 2/1/15 to 12/1/15 as 10 months and 12/1/15 to 12/2/15 as 1 day. 10M 1D. Both are perfectly valid answers.

You're making some assumptions here that are arbitrary. What are they?

• ###### 4. Re: Inaccurate Elapsed Time Calc - Help!

I wouldn't say I'm making any assumptions.  All I want is the exact calculation.  I was wrong earlier.  When I entered 02/01/2015 to 11/30/2015, it gave me 9 months and 1 day.  Which is absolutely wrong.  I don't see how "wrong" is arbitrary.  It should have been 9 months and 29 days.  I was incorrect when I wrote 30 days.  But, it all doesn't matter anyway because I found one that works.

http://www.briandunning.com/cf/57

• ###### 5. Re: Inaccurate Elapsed Time Calc - Help!

"I wouldn't say I'm making any assumptions.  All I want is the exact calculation. "

Well; you ARE making assumptions. It is not helpful to pretend otherwise. It helps to state them clearly.

There are multiple valid ways to approach this kind of problem and the different ways suit different needs.

For instance, the actual count of days in "nine months and 29 days" can vary.

For some applications, it may be useful and important to always get a consistent day count.

For others, it may not.

• ###### 6. Re: Inaccurate Elapsed Time Calc - Help!

Stephen,

It is 9 mos. 30 days only if 2/1/2015 is counted as day 1.

If someone is born on 2/1/2015, on 2/2/2015 they are 1 day old (but under your theory they would be 2 days old). The formula posted by FileMaker shows an elapsed time between 2/1/2015 and 11/30/2015 of 9 mos. 29 days, which is correct as far as elapsed time is concerned.

Al Quimby

1 of 1 people found this helpful
• ###### 7. Re: Inaccurate Elapsed Time Calc - Help!

Possibly I'm just too ignorant to know what you mean about assumptions.  I'm niether willfully misrepresenting nor "pretending" anything.  My request was answered, and through it I was able to find the correct solution for this application.

• ###### 8. Re: Inaccurate Elapsed Time Calc - Help!

Allen,

I'm beginning to see where the variations can come in.  I'm barely even a filemaker amateur, so I appreciate the added clarity!

• ###### 9. Re: Inaccurate Elapsed Time Calc - Help!

Let me try another way...

What is your expected result for a start date of 02/28/2015 and an end date of 3/31/2015?

1 Month

1 Month 3 Days

31 Days

All are valid based on your starting assumptions.

Good luck with that calc. A quick test shows you might have some issues with it.

• ###### 10. Re: Inaccurate Elapsed Time Calc - Help!

Stephen,

Thanks.

Looks like you need to decide if you want total days (counting first and last) or elapsed days (like someone's age).

AL

• ###### 11. Re: Inaccurate Elapsed Time Calc - Help!

You aren't kidding!  Now that you've opened my eyes, I do believe I want total days, counting the first and last.

• ###### 12. Re: Inaccurate Elapsed Time Calc - Help!

and dont forget to factor in leap day...

• ###### 13. Re: Inaccurate Elapsed Time Calc - Help!

Well I certainly have issues, that's for sure.  Allen (below) finally made me see what you meant as well.

It's difficult to write exactly what I mean but your example is helping.

If the dates were 02/01/2015 to 04/01/2015 then it should be 2 months and 1 day.

Now it gets tricky if I were doing 02/15/2015 to 03/15/2015.  Based on what I am doing, this would still be 1 month and 1 day.

Wow...just call me dense.  I was totally misunderstanding the assumptions bit of each comment. (sheepish)  I think I've even responded incorrectly to Allen.  This (above) is what I need exactly.

• ###### 14. Re: Inaccurate Elapsed Time Calc - Help!

Leap Years are no problem. FileMaker's date calcs handle Leap Years.

Al