ElapsedTime ( TStampBegin ; TStampEnd ; Format )
Calculates elapsed time from Timestamps
Average rating: 4.5 (31 votes) Log in to vote
|
Geoff Wells DataIsland Software LLC http://www.dataisland.com |
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, 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, Texas Aug 31, 2012 |
|
| Figured it out. Changed the calculation to... ElapsedSeconds = ( If ( IsEmpty ( TStampEnd ) ; Get ( CurrentHostTimestamp ) ; TStampEnd )) - TStampBegin ; |
||
|
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, 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, 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, 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, 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, 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, Muffetta's 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, UK Jan 7, 2018 |
|
| Brilliant, totally brilliant. Works a charm. TinyString works Ok for me. Geoff, you should be proud of yourself. |
||
 |
Tom Guise, Smart Wolf Jul 23, 2020 |
|
| Code containing all the "mods" from the comment above is here... // Input must be TimeStamp fields not strings. Format is a string Let ( [ ElapsedSeconds = TStampEnd - TStampBegin ; Days = Int ( ElapsedSeconds / 86400 ) ; Hours = Int ( ( ElapsedSeconds - ( Days*86400 ) ) / 3600 ) ; Minutes = Int ( ( ElapsedSeconds - ( ( Days * 86400 ) + ( Hours * 3600 ) ) ) / 60 ); Seconds = Int ( ElapsedSeconds - ( ( Days * 86400 ) + ( Hours * 3600 ) + (Minutes * 60 ) ) ) ; ElapsedDays = ElapsedSeconds / 86400 ; ElapsedHours = ElapsedSeconds / 3600 ; ElapsedMinutes = ElapsedSeconds / 60 ] ; Case ( Format = "Seconds" ; // Result is in number or time format ElapsedSeconds ; Format = "Minutes" ; // Result is a string ElapsedMinutes & " minutes" ; Format = "Hours" ; // Result is a string ElapsedHours & " hours" ; Format = "Days" ; // Result is a string ElapsedDays & " days" ; Format = "String" ; // Result is a string 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." ); 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 & " mi |
||
|
Tom Guise, Smart Wolf Jul 23, 2020 |
|
| nute, " ; 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." ); ); 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" ; "second" ) ; "" ); 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 ( |
||
|
Tom Guise, Smart Wolf Jul 23, 2020 |
|
| 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 & "." ; "" ) ) ) ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.