Time and timestamp arithmetic

Intermediate

Perform accurate calculations with Time and Timestamp fields, including duration, elapsed time, and cross-midnight handling.

What you'll learn

How FileMaker stores Time and Timestamp values internally
How to calculate elapsed time and express it in hours/minutes/seconds
How to handle time arithmetic that crosses midnight
How to add or subtract a duration to a Timestamp

FileMaker stores Time values as the number of seconds since midnight, and Timestamps as the number of seconds since January 1, year 1, at midnight. This means arithmetic works on both: subtract two Timestamps to get elapsed seconds, add seconds to a Timestamp to advance it. Knowing the underlying units is essential for converting durations to hours, minutes, and seconds.

1/4

Time arithmetic basics

Time fields store seconds since midnight. Subtracting two Time values gives elapsed seconds. Use Div() and Mod() to break it into hours, minutes, and seconds.

FileMaker Script

Let ( [
  elapsed = EndTime - StartTime ;  // result in seconds
  h  = Div ( elapsed ; 3600 ) ;
  m  = Mod ( Div ( elapsed ; 60 ) ; 60 ) ;
  s  = Mod ( elapsed ; 60 )
] ;
  h & "h " & m & "m " & s & "s"
)
// e.g., "2h 15m 30s"

Date calculations: age, duration, and working days Number formatting: dollar amounts, percentages, and display