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.
What would happen on weeks where Monday was a holiday? Presidents Day, Labor Day, etc. I believe that would break the formula on those days and cause a wrong calculation. Or even if the user called out sick on Monday and they were assigned the project on Friday, this would then calculate the weekends for that task assuming they started it during that week on anything but a Monday.
I don't have the answer here for that formula but just some things I noticed and thought would be worthwhile to bring up. This takes it much deeper if you had to bring in the timecard and holidays.
