Elapsed ( StartDate ; EndDate )

Elapsed Date to Text

Average rating: 4.0 (44 votes) Log in to vote

Lee Smith - Show more from this author

Sample input:

Elapsed(EndDate;StartDate)

2/28/2000 and 2/27/2004

Sample output:

3 Years 11 Months 29 Days

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

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

// This is an adaptation of the Answer ID 870

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=870&p_created=1115172477&p_sid=NA4UkaKh&p_lva=&p_sp=cF9zcmNoPTEmcF9zb3J0X2J5PSZwX2dyaWRzb3J0PSZwX3Jvd19jbnQ9NzExJnBfcHJvZHM9MSZwX2NhdHM9JnBfcHY9MS4xOzIudTAmcF9jdj0mcF9wYWdlPTEmcF9zZWFyY2hfdGV4dD1BZ2UgY2FsY3VsYXRpb24*&p_li=&p_topview=1

that will calculate the elapse time between two dates and convert it to text

Comments

		Nick Stockbridge, Transmedia Communications Ltd Aug 4, 2011
Works very well. Slightly trivial but unitary values show as plural e.g. 1 Years, 1 Months etc. But otherwise great.

		Michael Wallace, Empowered Data Solutions Aug 15, 2016
// Elapsed ( StartDate ; EndDate ) //Calculating Elapsed Time Between Two Dates Broken Into Year, Month, And Day //Edited to omit empty values, handle singular and plural correctly, and simplify month_length Let([ Number_of_Days = EndDate - StartDate ; Month_Length = Choose ( Month(StartDate) ; ""; "31"; "28"; "31"; "30"; "31"; "30"; "31"; "31"; "30"; "31"; "30"; "31" ) ; ~y = Int ( Number_of_Days / 365.25 ) ; ~y = Case ( ~y = 0 ; // Omitted "" ; ~y = 1 ; // Singular ~y & " Year " ; // Plural ~y & " Years " ) ; ~m = 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 )))))) ; ~m = Case ( ~m = 0 ; // Omitted "" ; ~m = 1 ; // Singular ~m & " Month " ; // Plural ~m & " Months " ) ; ~d = Round( Mod( Number_of_Days ; 30.4375 ) ; 0 ) ; ~d = Case ( ~d = 0 ; // Omitted "" ; ~d = 1 ; // Singular ~d & " Day" ; // Plural ~d & " Days" ) ]; ~y & ~m & ~d )

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Elapsed ( StartDate ; EndDate )

Comments

Support this website.

Support this website.