Brian Dunning's FileMaker Custom Functions

ElapsedTime ( TStampBegin ; TStampEnd ; Format )

Calculates elapsed time from Timestamps

  Average rating: 4.5 (31 votes) Log in to vote

Geoff Wells   Geoff Wells
DataIsland Software LLC
http://www.dataisland.com

Share on Facebook Share on Twitter

  Sample input:
ElapsedTime ( Timestamp ( "5/17/2004" ; "8:45:25 PM" ) ; Timestamp ( "5/18/2004" ;" 10:46:45 PM" ); "String" )
  Sample output:
1 day, 2 hours, 1 minute, 20 seconds.

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

Calculates the elapsed time between two Timestamps and presents the results in several selectable formats.

 

Comments

PW   PW, Texas
Aug 30, 2012
This seems to work if there is a timestamp in each field but it fails when the TStampEnd field is blank. Any ideas?
 
PW   PW, Texas
Aug 31, 2012
Figured it out. Changed the calculation to...

ElapsedSeconds = ( If ( IsEmpty ( TStampEnd ) ; Get ( CurrentHostTimestamp ) ; TStampEnd )) - TStampBegin ;
 
John Davis   John Davis, Houston
Jul 6, 2015
In the "String" format, there is a display error. The non-plural second text needs to be changed from " second, " to " second."
 
John Davis   John Davis, Houston
Jul 6, 2015
Added an additional "ShortString" format.

If, for example, your elapsed time is 3 minutes and 15 seconds, the "String" format will display this as "0 days, 0 hours, 3 minutes, 15 seconds."

Now, "ShortString" will not display the preceding zeroed time elements. It will more pleasingly display the example as "3 minutes, 15 seconds."

Place the following code directly after the Format="String" block of code.

;

Format = "ShortString" ;
// Result is a string of actual duration formats with no zero time formats preceding
Case (
Days;
// THEN
If ( Days = 1 ; Days & " day, " ; Days & " days, " ) & If ( Hours = 1 ; Hours & " hour, " ; Hours & " hours, " ) &
If ( Minutes = 1 ; Minutes & " minute, " ; Minutes & " minutes, " ) & If ( Seconds = 1 ; Seconds & " second." ; Seconds & " seconds." );

Hours;
// THEN
If ( Hours = 1 ; Hours & " hour, " ; Hours & " hours, " ) &
If ( Minutes = 1 ; Minutes & " minute, " ; Minutes & " minutes, " ) & If ( Seconds = 1 ; Seconds & " second." ; Seconds & " seconds." );

Minutes;
// THEN
If ( Minutes = 1 ; Minutes & " minute, " ; Minutes & " minutes, " ) & If ( Seconds = 1 ; Seconds & " second." ; Seconds & " seconds." );

Seconds;
// THEN
If ( Seconds = 1 ; Seconds & " second." ; Seconds & " seconds." );
)
 
John Davis   John Davis, Houston
Jul 7, 2015
Revised the above additional code, so that the "ShortString" format shows NO periods with zeros, so that "0 days, 1 hour, 3 minutes, 0 seconds." now displays as "1 hour, 3 minutes."

Additionally, I added the format "TinyString" to display abbreviated time periods. So, "1 hr, 3 mins, 45 secs." with the same above feature tha NO periods display with zeros.

The new code is in my next comment...
 
John Davis   John Davis, Houston
Jul 7, 2015
Place the following code directly after the Format="String" block of code:

;

Format = "ShortString" or Format = "TinyString" ;
// Result is a string of actual duration elements with no zero elements displayed (e.g., no 0 minutes, 0 seconds, etc.)
// TinyString additionally displays abbreviated periods (e.g., 3 mins, 1 sec.)
Let ([

DAYS.ISSET = If ( Days ; True ; False );
HRS.ISSET = If ( Hours ; True ; False );
MINS.ISSET = If ( Minutes ; True ; False );
SECS.ISSET = If ( Seconds ; True ; False );
HRS.PERIOD.DISP = If ( Hours ; If ( Format = "TinyString" ; "hr" ; "hour" ) ; "" );
MINS.PERIOD.DISP = If ( Minutes ; If ( Format = "TinyString" ; "min" ; "minute" ) ; "" );
SECS.PERIOD.DISP = If ( Seconds ; If ( Format = "TinyString" ; "sec" ; "seconds" ) ; "" );
DAYS.POSSESSION = If ( Days > 1 ; "s" ; "" );
HRS.POSSESSION = If ( Hours > 1 ; "s" ; "" );
MINS.POSSESSION = If ( Minutes > 1 ; "s" ; "" );
SECS.POSSESSION = If ( Seconds > 1 ; "s" ; "" );
DAYS.PUNCT.AFT = If ( HRS.ISSET or MINS.ISSET or SECS.ISSET ; ", " ; "." );
HRS.PUNCT.AFT = If ( MINS.ISSET or SECS.ISSET ; ", " ; "." );
MINS.PUNCT.AFT = If ( SECS.ISSET ; ", " ; "." )

];

If ( DAYS.ISSET ; Days & " day" & DAYS.POSSESSION & DAYS.PUNCT.AFT ; "" ) & If ( HRS.ISSET ; Hours & " " & HRS.PERIOD.DISP & HRS.POSSESSION & HRS.PUNCT.AFT ; "" ) & If ( MINS.ISSET ; Minute
 
John Davis   John Davis, Houston
Jul 7, 2015
The final IF statement code got cutoff in the comment above. Replace the above final IF statement with this code:

If ( DAYS.ISSET ; Days & " day" & DAYS.POSSESSION & DAYS.PUNCT.AFT ; "" ) & If ( HRS.ISSET ; Hours & " " & HRS.PERIOD.DISP & HRS.POSSESSION & HRS.PUNCT.AFT ; "" ) & If ( MINS.ISSET ; Minutes & " " & MINS.PERIOD.DISP & MINS.POSSESSION & MINS.PUNCT.AFT ; "" ) & If ( SECS.ISSET ; Seconds & " " & SECS.PERIOD.DISP & SECS.POSSESSION & "." ; "" )
)
 
John Davis   John Davis, Houston
Jul 7, 2015
Fix in the code given 2 comments up. The line:

SECS.PERIOD.DISP = If ( Seconds ; If ( Format = "TinyString" ; "sec" ; "seconds" ) ; "" );

should be:

SECS.PERIOD.DISP = If ( Seconds ; If ( Format = "TinyString" ; "sec" ; "second" ) ; "" );

The change in the line is that the plural "seconds" should be the singular "second".
 
Troy Krueger   Troy Krueger, Greenwood Lake
Aug 29, 2016
I can't seem to put the 'ShortString' in the right place for it to work. I put it before the very last of the original code.

HERE
)
)

it 'takes' it but it doesn't work. any help is appreciated
 
Alistair Hay   Alistair Hay, UK
Jan 7, 2018
Brilliant, totally brilliant.
Works a charm.
TinyString works Ok for me.
Geoff, you should be proud of yourself.
 

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.