3 Replies Latest reply on Nov 20, 2013 10:44 AM by andruziwa

# calculating the difference between times

Could anyone offer some suggestions for how to calculate the difference between times?

I have played around, searched around the internet, searched BrianDunning.com for custom functions and failed.

I want to see if appointment records for the same day are greater than 30 minutes or 60 minutes behind the current time. I am using this to conditionally format a portal list of appointments to highlight appointments that are supposed to have happened.

I am currently using the expression: get (current time) - local_appts::appointment time > 0 to successfully highlight appts that are past due but I cannot differentiate between ">0" and ">30" or ">60."

• ###### 1. Re: calculating the difference between times

Hello, andruziwa.

What you'll want is the Case function. This will allow you to set up a series of conditions, along with their outcomes if true. For example:

Case (

Get ( CurrentTime ) - local_appts::appointment time > 60 ; ">60" ;

Get ( CurrentTime ) - local_appts::appointment time > 30 ; ">30" ;

Get ( CurrentTime ) - local_appts::appointment time > 0 ; ">0" ;

""

)

This tells FileMaker to choose the first true condition. (That's why they're in the order they're in.)

For better (faster) execution, use the Let function to call the Get ( CurrentTime ) function and evaluate the related field only once:

Let ( [

rightNow = Get ( CurrentTime ) ;

apptTime = local_appts::appointment time

] ;

Case (

rightNow - apptTime > 60 ; ">60" ;

rightNow - apptTime > 30 ; ">30" ;

rightNow - apptTime > 0 ; ">0" ;

""

)

)

HTH

Mike

Edit: For Conditional Formatting purposes, you can put the same conditions in. Just remember that Conditional Formatting tests are executed in the order they appear, and all true tests are applied. So you'll want to put the tests in the reverse order of the Case statement above. That way, the >60 test will evaluate last and whatever formatting you apply through it will be sure to appear.

• ###### 2. Re: calculating the difference between times

Duh. Missed the "minutes" part. Pixi is right; time calculations work in seconds, so you have to multiply your minutes by 60 to get the correct answer.

Mike

• ###### 3. Re: calculating the difference between times

thanks to both of you for this rapid and awesome answer.  much appreciated.