7 Replies Latest reply on Apr 1, 2016 4:27 AM by bigtom

# Converting days into yrs, mnths, days

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?

• ###### 1. Re: Converting days into yrs, mnths, days

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 )

• ###### 2. Re: Converting days into yrs, mnths, days

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"

• ###### 3. Re: Converting days into yrs, mnths, 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).

• ###### 4. Re: Converting days into yrs, mnths, days

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

• ###### 5. Re: Converting days into yrs, mnths, days

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

• ###### 6. Re: Converting days into yrs, mnths, days

And this looks interestig too!