Brian Dunning's FileMaker Custom Functions

Elapsed ( StartDate ; EndDate )

Elapsed Date to Text

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

Lee Smith   Lee Smith

Share on Facebook Share on Twitter

  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)

// 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   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   Michael Wallace, Dallas, Texas
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
)
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.