Attention: Apricot is now Bonterra Case Management. Please bear with us as we update our screenshots to match our new name.
Due to MySQL limitations within Bonterra Case Management, it is not currently possible to calculate the time difference that spans across multiple days. This article will outline how to workaround this limitation using multiple fields and form logic.
On its own, a Calculation Time Difference field will always assume that the times given are from the same day which will cause problems when your times pass midnight. Ex: 11:30pm (03/31/2023) and 12:30am (04/01/2023). The resulting value will be incorrect as the Calculation Time Difference field will find the difference in times times (11:30pm and 12:30am) as if they are from the same day.
With the addition of some fields and form logic we are able to work around this limitation by finding the time spent on the first day and adding it to the time spent on the second day. This article will use the 12 hour setting for time fields, but the process will still work for 24 hour time fields.
In total, the workaround will need:
4 Time fields
1 Numeric field
2 Time Difference Calculation fields
1 Calculation (+) Add field.
First add the Time fields. The Time fields will be your start time, end time (or end of the day), start of the next day, and the end time. The first end time field will need users to enter 11:59PM when the true end time is on the next day. It is recommended to add that information to the field's tool tip in the field properties.
Next, add the Numeric field and set its Default Value to 0. This field will be used to correct for the lost minute when using 11:59PM in one of the Time fields.
After the Numeric and Time fields are set up on your form, add in the Calculation Time Difference fields. The first calculation field will find the difference between the first start time and the first end time. The other calculation will be for the second start and end times.
The Calculation Time Difference fields will give numeric values that we will use with the Calculation (+) Add field. Set up this calculation to add both of the Calculation Time Difference fields along with the Numeric field.
After the fields have been added, the form will also need two form rules to set values in fields so we get an accurate calculation. The first rule will set the 2nd day's start time to midnight, and the second rule will compensate for the missing minute in the calculation fields.
The first rule will need the following Conditions:
[1st start time field] Is Not Empty
[1st start time field] Is Not 12:00
The rule's Action needs to be:
Set Value of [2nd Start Time] to [12:00]
The other rule will need the following Conditions:
[2nd end time field] Is Not Empty
[2nd end time field] Is Not 12:00
The rule's Action needs to be:
Set Value of [Numeric Field for Time Calculations] to [1]
When everything has been added, publish the form and your new records will calculate the correct time differences. If these changes were made to a form that already has records made, the existing records will not automatically update their data with the new calculation fields. For more information, here is our article for Updating Records After Adding Calculation Fields To An Existing Form.