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...
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.
For the Monday holidays, I created a field Text formula field that will show yes when the disposition date/time field is the day after one of the Monday holiday. When this field is equal to yes, the Assign to initial contact w/in 24 hours field will minus 72 hours instead of 24 hours.
