Im subtracting a date from another date resulting in number of days.
Need to convert this into yrs, months & days. Who wants to tell me how?
Why re-invent the wheel? FileMaker does this math perfectly fine using native functions.
GetAsDate ( _EndDate - _StartDate ) gives you the difference between the dates as a date.
The rest is simply a question of formatting the output whatever way you like - erolst's formula contains most of the elements you will need for that - you just need to use the Year ( _Date ), Month ( _Date ) and Day ( _Date ) functions to parse out each element from GetAsDate ( _EndDate - _StartDate ).
I suppose it depends if you are going to use an average days per month or if you need to calculate the exacte number of days as it relates to the exact dates you started with.
Why not use this custom function? FileMaker Custom Function:DateDifference ( date1 ; date2 ; format )
You would use the "long" format to have just Years, Months, Days
DateDifference ( 29/02/2000 ; 31/03/2006 ; "long" ) will return: "6 years, 1 months, 2 days"
If you just need a ballpark figure, try this:
Let ( [
x = your subtraction result ;
y = Div ( x ; 365 ) ;
yString = Case ( y ; y & " year" & Case ( y > 1 ; "s" ) ) ;
m = Div ( Mod ( x ; 365 ) ; 30 ) ;
mString = Case ( m ; m & " month" & Case ( m > 1 ; "s" ) ) ;
d = x - y * 365 - m * 30 ;
dString = Case ( d ; d & " day" & Case ( d > 1 ; "s" ) ) ;
res = List ( yString ; mString ; dString )
Substitute ( res ; ¶ ; ", " )
If you need an exact result (actual number of y,m,d passed), then a mere number won't suffice; you'd have to compare the dates first and do more complicated calculations (like in that CF).
Bigtom and Erolst, thanks to both of you! The easier way (erolst) worked for me. I'm going to study bigtoms suggestion too but since i'm rather new to this, don't know if i'll ever quite figure it out??
And this looks interestig too!
David has your best/easiest answer.
Custom functions require FMP Advanced and that one has built in formatting options. David's suggestion is the cleaner way to get it done.
Retrieving data ...