I want to calculate duration from 2 dates and 2 times. The span of time for each day is 8am-8pm; hours after 8pm should not be counted.

• 0
• Question
• Updated 2 years ago
• In Progress
I need help. I am trying to calculate duration from 2 dates and 2 times.However the span of time for each day is 8am-8pm. The hours after 8pm should not be in the calculation.
• 80 Points

Posted 2 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 69,824 Points
I don't have an instant answer for you, but do weekends count?  I have done these formuals before and they tend to be very long and messy.
• 80 Points

No weekends, please. I have the basic formula however I need the dates results without hours after 8pm.

Example: [ Actual End Time And Date]-[Actual Start Time And Date]
(Edited)
• 31,698 Points
Create a formula-duration field.  Insert this formula.  This is assuming your start and end date/time fields are called [Start Date] and [End Date], respectively.

# of Weekdays + # of hours in 1st Day + # of hours in the last Day - 1 day to balance the ends

```Days(WeekdaySub(ToDate([End Date]), ToDate([Start Date])))
+
(ToTimeOfDay("8 PM")-ToTimeOfDay([Start Date]))
+
(ToTimeOfDay([End Date])-ToTimeOfDay("8 AM"))
-
Days(1)```

This excludes weekends, and assumes your "time" is between 8 am & 8 pm.  If you are going to have times outside of those windows we'd just need to put some conditions on that part.

Let me know if you need more, but I'd like to know what you are going to use these durations for?  What are you timing out of curiosity?﻿
(Edited)