Name & Parameters: [color:red][big] Elapse ( StartDate ; EndDate ) [/big]

Description: I posted this in www.briandunning.com a while back, but forgot to post it here.

This is an adaptation to a FileMaker Tech Article ID 870.

Sample Input:

2/28/2000 and 2/27/2004

Results:

3 Years 11 Months 29 Days

Recursive: no

Formula:

//Calculating Elapsed Time Between Two Dates Broken Into Year, Month, And Day



Let(

Number_of_Days = EndDate - StartDate;

Let(

Month_Length =Case(Month(StartDate) = "1"; "31"; Month(StartDate) = "2" ; "28" ; Month(StartDate) = "3" ; "31" ; Month(StartDate) = "4" ; "30" ; Month(StartDate) = "5" ; "31" ; Month(StartDate) = "6" ; "30" ; Month(StartDate) = "7" ; "31" ; Month(StartDate) = "8" ; "31" ; Month(StartDate) = "9" ; "30" ; Month(StartDate) = "10" ; "31" ; Month(StartDate) = "11" ; "30" ; Month(StartDate) = "12" ; "31" );

(Int( Number_of_Days / 365.25 )) & " Years " & If((Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) <= Month_Length and ((Int(Number_of_Days / 30.4375))) < "1" ; "0" ; ((Int(Number_of_Days / 30.4375) - (12 * (Int( Number_of_Days / 365.25 ))))))

 & " Months " & (Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) & " Days")

)



 

Required Functions:

Author(s): Lee Smith

Date: 06/13/06

Credits: Someone at FMI provided the Article

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

ElapseCF_Sample.fp7.zip

Because I am interesting I have test your formula it works nice but...

In a discussion of the forum we have mentioned that FM miss a day when we calculate for example:

1/1/2006 until 31/12/2006 because the last day of the year ends in 12 (midnight). In my database I use this to correct the time that FM missing when the difference of dates is > year. As I notice also elapse does not correct the 28 or 29 days of February.

I make this custom function:

Employ(DateEnd; DateStart)

Let ([ TotalOfDays=DateEnd-DateStart+1,75;

y=Int(TotalOfDays/365,25);

m=Int(Mod(TotalOfDays; 365,25)/30,4375);

d=Int( TotalOfDays-(y * 365,25)-( m * 30,4375))];

y & " Years," & " " & m & " Months," & " " & d & "Days ")

Enjoy

Pascal

Edited February 26, 200718 yr by Guest