Calculate Weekend Hours in a duration of Date/Time Field

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
I will try to explain this the best that I can. I am trying to calculate the number of hours between two date/time fields excluding weekends. I have seen quite a bit of chatter, but no solution to the problem. 

I've come closest by using the following post as a guideline:
https://community.quickbase.com/quickbase/topics/calculate-duration-excluding-weekends-dependent-fie...

However, I'm running into some trouble. While I can get it to tell me the number of weekend days and subtract that, I need it to tell me weekend hours. The idea here is to gather data on how long it takes to go from submitted to approved. So if I have 72 hours to approve a form that should exclude weekends, because I don't work weekends. 

However, the form can be submitted at any time weekends included. So my submission date could be on a weekend. However, the approval date should only be on a weekday. So the duration between the submission and approval dates should exclude weekends. 

For example, I have a
submission date/time = 5/12/18 9:06 AM
approval date/time = 5/14/18 8:05 AM

So a simple duration calculation between the two fields would return 46 hours and 59 minutes, the actual time of approval should be 8 hours and 5 minutes when only weekdays are included.


I am using the following formula which would exclude an entire 48 hours in this instance, thus returning a value of -1 hour and 1 minute:


var Duration HoursInclude = Max(Hours(0), [FSR Sent Date]-[Approval 1 and 2 Complete Time]);

var Number HoursExclude = ToDays(ToDate([FSR Sent Date]) - ToDate([Approval 1 and 2 Complete Time])) - WeekdaySub(ToDate([FSR Sent Date]), ToDate([Approval 1 and 2 Complete Time]));

$HoursInclude - Hours($HoursExclude * 24)


Does anyone have any thoughts on how to get this to work the way I need it to?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Try something like this (this will handle if its submitted ON the weekend. (starting point, I'll have to come back to deduct the weekend time if its submitted on a Monday kind of thing)


var date SubDateOnly=ToDate([Date/Time Submitted]);  //convert date/time to date

var datetime NewSubmitted=ToTimeStamp(ToWeekdayP($SubDateOnly), ToTimeOfDay("11:59 pm"));  //create a friday night at midnight timestamp

var datetime AdjustedSubmitted=If(IsWeekday($SubDateOnly), [Date/Time Submitted]), $NewSubmitted);  //If its a weekday, use the regular timestamp, otherwise use the friday night time.

$AdjustSubmitted-[Date Created]  //put it all together


If your submittals are never greater than a week, then you could add some conditional logic to evaluate the start and end, and if the total is greater than x number (i.e. it includes a weekend time) subtract that 48 hours.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
This might be better:

WeekdaySub
 (Date d2, Date d1)

Description: Returns the number of weekdays in the interval starting with d1 and ending on the day before d2 (same as subtracting Dates, but the result is the number of weekdays instead of a Duration). It is the inverse of WeekdayAdd.


Example: WeekdaySub([Finish], [Start]) returns the number of weekdays between the dates in the Start and Finish fields.

WeekdaySub(ToDate("6/24/2003"), ToDate("6/20/2003") ) returns 2
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I just did this one for an internal project.  It shows the number of weekdays from the start to closure and a running total if its not closed yet.


var datetime Start=[Approved On];
var datetime End=If(IsNull([Completed / Closed On]), Now(), [Completed / Closed On]);

var date SDate=ToDate($Start);
var date EDate=ToDate($End);

var number NumberOfDays=WeekdaySub($EDate, $SDate);

Days($NumberOfDays)
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
Your first response appears to have solved my problem! I will monitor for a while to be sure, but it looks good as of now. Thank you!