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?

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?

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

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.

/* Original algorithm © 2006 Winfried Huslik, www.fmdiff.com Modifications © 2011 Brett Buchmiller flag ; "Y" returns total years, "M" returns total months, "D" returns total days, "T" returns time as years, months days as text with correct grammer flag ; "LIST" returns return delimited list with y, m, d Values returned are completed integer units - rounded DOWN. */

Let ( [

neg = Case ( date1 > date2 ; -1 ; 1 ) ; d1 = Case ( neg < 0 ; date2 ; date1 ) ; d2 = Case ( neg < 0 ; date1 ; date2 ) ; d = Mod ( Day ( d2 ) - Day ( d1 ) ; Day ( Date ( Month ( d1 ) + 1 ; 0; Year ( d1 ) ) ) ) ;

m = Mod ( Month ( d2 ) - Month ( d1 ) - ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) - ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) ) ;

yearlabel = If( y=1 ; y & " year" ; y & " years" ) ; monthlabel = If( m =1 ; m & " month" ; m & " months" ) ; daylabel = If( d=1 ; d & " day" ; d & " days" ) ];

Case(

flag = "Y" ; y * neg ; flag = "M" ; m * neg + (y * 12 * neg) ; flag = "D" ; ( date2 - date1 ) * neg ; ( flag = "T" ) ; Case( ( y ≠ 0 ) and ( m ≠ 0 ) and ( d ≠ 0 ) ; yearlabel & ", " & monthlabel & " and " & daylabel ; ( y = 0 ) and ( m ≠ 0 ) and ( d ≠ 0 ) ; monthlabel & " and " & daylabel ; ( y = 0 ) and ( m = 0 ) and ( d ≠ 0 ) ; daylabel ; ( y = 0 ) and ( m = 0 ) and ( d = 0 ) ; "0 days" ; ( y ≠ 0 ) and ( m = 0 ) and ( d ≠ 0 ) ; yearlabel & " and " & daylabel ; ( y ≠ 0 ) and ( m ≠ 0 ) and ( d = 0 ) ; yearlabel & " and " & monthlabel ; ( y ≠ 0 ) and ( m = 0 ) and ( d = 0 ) ; yearlabel ; ( y = 0 ) and ( m ≠ 0 ) and ( d = 0 ) ; monthlabel ; "Undefined Date Text Layout!" ) ; flag = "LIST" ; y * neg & "¶" & m * neg & "¶" & d * neg & "¶" ; "Undefined Flag!" ) )

This is equivalent to a calculation of age between two dates so:

Let ( [

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

Let ( [

beg = "

**0**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"

)

Function GetAsDate ( date ) would have been safer

So if you want to adapt for an age calculation it would be:

Let ( [

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"

)

Unstored calculation

General comment -- by not using existing libraries when they exist (usually!) the developer:

- Wastes time (some times a LOT of time) re-creating the wheel for code that already exists for the data type in question (date API, in this case),
- Creates an FMP-only solution.
- Often, doesn't get the right answers in various edge (or other) cases. Date logic in particular can be extremely tricky to get correctly craft yourself.

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

Year (date_2) - Year (date_1)

or

(date_2 - date_1)/365.2525