Basic time math formula...which function

I’m trying to do basic time math. This should be simple, but I’m missing something

3 fields called «Clock In», «Clock Out» and a third field called «Difference». All 3 fields are text fields.

The formula timedifference(time(«Clock In»),time(«Clock Out»)) works in the Formula Workshop window.

It does not work either by trying Morph fill with Formula or by writing the formula into the Field Properties.

I’m sure the fix is simple, but I don’t see it.


Timedifference( returns a number, but you said your Difference field was text. Since you said it worked in the Formula Workshop, I’m assuming you want the result to be the number of seconds in the interval. To get that as text, wrap it all in the str( formula.

str(timedifference(time(«Clock In»),time(«Clock Out»)))

If that’s not how you want it formatted, check out the timepattern( and texttimedifference( functions.

Thank you for the quick response. I wasn’t clear.

The problem I’m having is populating the «Difference» field with the result. I thought I could use Morph fill with Formula but that is not working.

I was hoping to do this without a procedure.

The reason you are having trouble populating the Difference field with the result is that the Difference field is text, and your result is an Integer. The datatypes need to match.

It’s certainly possible to do this without a procedure. But I do want to point out that creating procedures is super easy, and often it’s easier to solve a task with a short procedure than using a dialog - especially if there is any chance that it will take you more than one try to get it to work.

Here is the procedure you would need. As you can see, it is quite simple.

field Difference
formulafill str(timedifference(time(«Clock In»),time(«Clock Out»)))

To solve this without a procedure, you would

  • Click anywhere in the Difference field
  • Open the Fill with Formula dialog
  • Type in the formula str(timedifference(time(«Clock In»),time(«Clock Out»)))

Basically the same amount of work. But an advantage of using a procedure is that if you make a mistake, you can quickly edit the code and re-run the procedure, rather than having to re-open the dialog and start from scratch each time.

Even if you’re only planning to perform the task once, it’s very easy to create a procedure, use it, then delete it if you’re never going to use it again. You don’t even need to give it a name.