Calculating Time excluding Work Hours

Help Center Get Answers
1

I am trying to use an equation field to calculate how old a record is. For example, I have a date created field for a record and an equation that uses the “current time” function to calculate how old the order is:

equation field = currentdate()-{date created}

This works great at calculating the total hours. However, I am looking to exclude working hours from the total. Work hours are 9am to 5pm. Can anyone purpose a solution to this?

Thanks in advance!

2

Hi, @RyanL!

This is a great question, and thank you for sharing!

I searched a bit in the forum to see if anyone else has posted a solution for this, and I came across this post that might be helpful to you: Calculate time between dates in PARTIAL days or WORKING HOURS?

There’s a lot of fields involved in their app to accomplish a similar goal, so I can share a bit of a summary of their approach and share some screenshots of some of the major equations involved. It’s complicated, but I’m hoping that some of these details can nudge you in the right direction.

  • They created a “Start Date Only” and a “End Date Only” field which are both Date fields that ignore time.

  • Their equations involved utilize the netWorkDays() function.

  • There’s one Equation field used that is setup like this:

    image
    image1050×1362 57.4 KB

  • Then, there’s another Equation field set up like this:

    image
    image1078×1438 53.1 KB

  • Those two above Equation fields are then used in another Equation field set up like this:

    image
    image1082×1378 50.3 KB

  • This setup calculates the total working hours for their use case, but this method could be used to exclude working hours from the total. The part that would get extra complicated would be to take into account when the working hours are for a day instead of just subtracting 8 hours from each day.

If this doesn’t help lead you in the right direction to accomplish your goal, I recommend reaching out to a Knack Expert. In that thread, one of our Knack experts was involved in the solution and could possibly be of help to you with your app as well. Here is the link to Sunny’s details: Knack Experts Network

I hope this info helps and happy Knacking! :slight_smile:

1 Like
3

Thank you for the response. I reached out sunny and he was able to provide a solution. Much more complicated than I anticipated, but @Sunny_Singla got it done!

2 Likes