Calculating duration between appointment times in knack

Help Center Get Answers
1

I am trying to create an equation field to capture the amount of time from when a record is created and when a status field is changed to “Scheduled”. I have a “date/time created” field and a “appointment date/time” field. Both fields settings are set to display date and time.

I need to calculate the number of work hours between these two fields. Work hours are considered as 8am to 6pm daily, Monday to Friday.

Does anyone know of a solution to complete this?

Thanks in advance!

2

Hi Ryan,

Thank you for reaching out to us! This can be done by using two Date Equations:

  1. Calculating the number of hours (date created + appointment date)
  2. Calculating the number of days and multiplying it by 14 (these are non-business hours)

Then subtract the result of #1 - #2

Equation 1 will count every hour, not just 8am to 6pm, so equation 2 will count the number of non-business hours and multiplying them by the number of days so that you can then take those non-business hours from the equation 1

Please let us know if you have further questions!

1 Like
3

This doesn’t look like it works. The first step doesn’t generate the correct result. I have an equation field with the equation type set to date and in the equation editor I have: {Date Created}+{Appointment Date}

The amount of time is way off. For example, I have a record with a date created of 05/22/2023 3:32pm and an appointment date of 05/23/2023 1:00am and the result of the equation is 935,992.53.

4

Hey Ryan

Can you please pop us a support ticket so that we can go in and check these fields for you and work something out?

5

i have the same question so would appreciate any answers…

I have two time and date fields - start and end

I have selected the following:

Equation Type
Date

Date Type
Hours

Result Type:
Number

then in equation
{End time} - {start time}

but the answer i get is in the thousands - which even if seconds is totally incorrect

6

Looks like the issue is persistent when you try adding the equation field that has the total to a form. If you add it to a details view it is correct. Also, if you have a large dataset, the equation takes a short time to update all the records.

7

Thanks Nick, but I don’t understand point 2. What am I using to calculate the days? Am I using the same fields but just changing the output to days instead of hours? More detail would be helpful. Thanks in advance!

8

Hi, @RyanL and @AndrewUK!

Thank you for sharing your questions here!

This may require taking a look at your apps and setups, and we would love to further help you with finding a solution for this. Please submit these details to the support team either via the chat widget in the Builder of your app or via this Form. Thank you for your patience!

Have a great week! :slight_smile:

1 Like