Duration between two dates excluding weekends by hours/mins instead of days
I am trying to figure out the duration between two date/time fields and I want to exclude the weekends but I don't needs days, I need hours and mins. Everything that I have found so far says to use th...
To me, the easiest thing would be to break it down into smaller bits:
Set the Units on your Duration field properties from 'Smart Units' to 'HH:MM'
Use WeekdaySub to get the # of Weekdays between the two dates
Calculate the number of minutes between the starting value and midnight of that day, if the Starting value is a weekday
Calculate the number of minutes between 00:01 and the ending value, if the Ending value is a weekday
Add 2, 3, and 4 together
------------------------------ Blake Harrison bharrison@datablender.io DataBlender - Quickbase Solution Provider Atlanta GA 404.800.1702 / http://datablender.io/ ------------------------------
When I use a duration formula, it will not let me use the WeekdaySub and to get HH:MM you have to use a duration formula. When I try and use a numeric formula the HH:MM is not an option.
Here is the solution to this issue: I created a field that would pull the day of the week from the Assignment field then created another field that would pull the day of the week from the 1st Disposition Time/Date field.
(Text formula field type: Case(DayOfWeek((ToDate([Datefield]))),1,"Monday",2,"Tuedsay",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")
In my Assign to initial contact w/in 24 hours field this is how I created my formula: (Duration formula type) I only want it to take the weekend out if the entry was assigned on a Friday and the person assigned didn't work the entry until Monday (first part of the formula), otherwise it should calculate as normal. The -Hours(48) removes the weekend hours.
