SubtractTimes ( Time1 ; Time2 )

Subtract one time from another; both times in h:mm:ss format

Average rating: 4.8 (23 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

SubtractTimes ( "59:43" ; "28:13" )
SubtractTimes ( "2:01:58" ; "29:54" )

Sample output:

31:30
1:32:04

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

/*---------------------------------------
# SubtractTimes ( Time1 ; Time2 )
# Time2 will be subtracted from Time1
# Entry format should be h:mm:ss, or mm:ss, or ss (up to 86400)
# Result will be in same format, dropping unneeded info from left
# Examples: SubtractTimes ( "28:35" ; "35:47" ) = -0:07:12
# SubtractTimes ( "59:43" ; "28:13" ) = 31:30
# SubtractTimes ( "2:01:58" ; "29:54" ) = 1:32:04
# 2019 changes: Corrected so it worked for :01, de-complicated CF, minor error-trapping
# Bill Thurmes 2014 corrected 3/21/2019
------------------------------------------*/

Let ( [ 
Time1 = Substitute ( Filter ( Time1 ; "0123456789-+:" ) ; "::" ; ":" ) ;
Time2 = Substitute ( Filter ( Time2 ; "0123456789-+:" ) ; "::" ; ":" ) ;
Time1 = Case ( 
		PatternCount ( Time1 ; ":" ) = 2 ; 
		GetAsTime ( Time1 ) ; 
		
		Time1 < 0 and PatternCount ( Time1 ; ":" ) < 2 ;
		GetAsTime ( Evaluate ( Substitute ( Time1 ; ":" ; " * 60 - " ) ) ) ;
		
		PatternCount ( Time1 ; ":" ) < 2 ; 
		GetAsTime ( Evaluate ( Substitute ( Time1 ; ":" ; " * 60 + " ) ) ) 
		) ;
Time2 = Case ( 
		PatternCount ( Time2 ; ":" ) = 2 ; 
		GetAsTime ( Time2 ) ; 
		
		Time2 < 0 and PatternCount ( Time2 ; ":" ) < 2 ;
		GetAsTime ( Evaluate ( Substitute ( Time2 ; ":" ; " * 60 - " ) ) ) ;
		
		PatternCount ( Time2 ; ":" ) = 1 ; 
		GetAsTime ( Evaluate ( Substitute ( Time2 ; ":" ; " * 60 + " ) ) ) 
		) ;
~t = Time1 - Time2 ] ;
~t
)

Time2 will be subtracted from Time1
Entry format should be h:mm:ss, or mm:ss, or ss.
Result will be in same format, dropping unneeded info from left.
Primarily useful if data is/might be in a mm:ss format
It now gives the correct answer if Time1 is earlier than Time2
Changed 4/12/19 to work better

Comments

		comment, VR Apr 10, 2014
Filemaker can add and subtract times natively.

		Bill Thurmes, MDCA Apr 11, 2014
Thanks, VR. I had needed something to subtract text that was in a mm:ss format, which Filemaker's native time addition doesn't deal with well. I've changed the script to use Filemaker's native capabilities.

		K.C., San Francisco Oct 17, 2014
Is there a way to accept non military time? If time 1 occurs before noon, and time 2 occurs after noon, the results are in the wrong direction.

		Bill Thurmes, MDCA Oct 22, 2014
I assume you want a negative result when Time1 is before Time2? That it didn't work before was a bug in my function; in the final case statement I had forgotten to apply sign to t; I've fixed that now.

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

SubtractTimes ( Time1 ; Time2 )

Comments

Support this website.

Support this website.