Hello FM Community,
I'm trying to use two dates to calculate the difference in years. For example,
11/11/1943 - 8/29/1989 = 46 years
Any suggestions on how to accomplish this?
Year (date_2) - Year (date_1)
(date_2 - date_1)/365.2525
If you want the number of FULL years between the dates, subtract the years and then subtract an additional 1 year if the endDate is before the startDate when put in the same year (I picked 2000 because it's a leap year).
Year ( endDate ) - Year (startDate ) -
( Date ( Day ( endDate ) ; Month ( endDate ) ; 2000 ) < Date ( Day ( startDate ) ; Month ( startDate ) ; 2000 ) )
Would this calculate the leap years that occurs every 4 years before or after the year 2000?
FileMaker always considers leap years in date calculations.
I'm not sure what you mean but I think you're asking the wrong question. Lets walk through an example using your dates 11/11/1943 - 8/29/1989.
The first part of the formula is Year ( endDate ) - Year (startDate )
This evaluates to 1989 - 1943 and then that evaluates to 46
The second part of the formula is ( Date ( Day ( endDate ) ; Month ( endDate ) ; 2000 ) < Date ( Day ( startDate ) ; Month ( startDate ) ; 2000 ) )
This evalutes to 11/11/2000 < 8/29/2000 which then evaluates to FALSE or 0, so no years are deducted.
Any leap year could have been used in place of 2000 - it will not affect the result. If you do not choose a leap year, then you would get an incorrect result if the end date is Feb 29 and the start date is March 1 (a year should be deducted but would not be because both dates are read as March 1).
Here's a custom function that will accomplish the task.
Let ( [
Date ( Month ( d1 ) + 1 ; 0; Year ( d1 ) ) ) ) ;
( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;
( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) ) ;
This is equivalent to a calculation of age between two dates so:
beg = "8/29/1989" ;
end = "11/11/1943"
GetAsText ( Year ( beg ) - Year ( end ) - If ( beg < Date ( Month ( end ) ; Day ( end ) ; Year ( beg ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( beg ) - Month ( end ) + 12 - If ( Day ( beg ) < Day ( end ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( beg ) - Day ( end ) + If ( Day ( beg ) ≥ Day ( end ); 0 ; If ( Day ( beg - Day ( beg ) ) < Day ( end ) ; Day ( end ) ; Day ( beg - Day ( beg ) ) ) ) ) & " Days"
Answer is 46 Years, 9 Months, 18 Days
There seems to be something off with your CF.
According to this website (see URL below), your CF seems off by one year.
Second check: Using a real date API, my micro-service agrees with both the YDM and the overall total days above it:
So, here are two results using date APIs that would be commonly used in real world applications (45 years plus months and days).
Not 46 years, but 45 Years, 9 months, and 18 days.
Unlike some of the hugely long-winded CFs that re-create the wheel (that is attempt to duplicate long-ago-created code in standard libraries ready to use), this calculation is literally one line of regular Java code you could use from FMP via a micro-service. The benefits of using a micro-service are extreme...including, in this case, a real date API.
For more information about micro-services, see my tutorials at:
Create Micro-Services Using Java and the Spark Java Framework
The Simplest Micro-Service! (Python and Flask)
Sorry I omitted a zero for the month 8/29/1989 when I wrote it in Data Viewer, it should be
beg = "08/29/1989" ;
Function GetAsDate ( date ) would have been safer
So if you want to adapt for an age calculation it would be:
todaysDate = Get ( CurrentDate ) ;
DOB = GetAsDate ( Person_DOB )
GetAsText ( Year ( todaysDate ) - Year ( DOB ) - If ( todaysDate < Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( todaysDate ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( todaysDate ) - Month ( DOB ) + 12 - If ( Day ( todaysDate ) < Day ( DOB ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( todaysDate ) - Day ( DOB ) + If ( Day ( todaysDate ) ≥ Day ( DOB ); 0 ; If ( Day ( todaysDate - Day ( todaysDate ) ) < Day ( DOB ) ; Day ( DOB ) ; Day ( todaysDate - Day ( todaysDate ) ) ) ) ) & " Days"
Sorry I must be not be awaken yet, I read half of your question, so to get only years the calculation is:
beg = GetAsDate ( "08/29/1989") ;
end = GetAsDate ( "11/11/1943")
Year ( beg - Date ( Month ( end ) ; Day ( end )-1; Year ( end ) ) )-1
Result is 45
General comment -- by not using existing libraries when they exist (usually!) the developer:
One look at the various implementations in, for example, the Java source code, would quickly discourage most devs from trying to re-create the wheel and trying to approach the rigor, robustness, elegance, thought, and time put into that (existing, ready-to-use) library logic.
I know, I'm the sound of one hand clapping on this topic (on this forum).
Here are a few test cases I created a while back (1,000 of them in total):
I don't have a computer in front of me but if you would put the most recent date first, it works.
Year (date_2) - Year (date_1) is not correct as it gives 46 years.
Yes, you're right.
My service method abstracts that detail so it doesn't matter the order you enter the dates.
I did a quick check (generating all 1,000 year values using your CF) and didn't see any differences.
I am afraid that the Correct Answer chosen won't work for leap years as a leap year has 366 days
Your CF matched the Java Date API in all 1,000 test cases.
Your CF is the correct answer in my view.
Retrieving data ...