Jump to content

Elapse ( StartDate ; EndDate )


This topic is 5394 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

  • 8 months later...

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 by Guest
Link to comment
Share on other sites

This topic is 5394 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.