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
  • 1
  • 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.
Photo of Sheryl Young

Sheryl Young

  • 80 Points 75 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.
Photo of Sheryl Young

Sheryl Young

  • 80 Points 75 badge 2x thumb

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)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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)