Duration between two dates excluding weekends by hours/mins instead of days

Question

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 they numeric formula and weekdaysub. But with this, I only get days. Is there a way to either use the duration formula or use the numeric formula with the weekdaysub and format it to hours/mins?
Below is a screenshot of the formula I have now, it is including the weekends:

------------------------------
Nicole Varner
------------------------------

blakeharrison · Answer

To me, the easiest thing would be to break it down into smaller bits:&nbsp;

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&nbsp;

​​------------------------------Blake Harrisonbharrison@datablender.ioDataBlender - Quickbase Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

nicolevarner · Answer

Can you please show me what this would look like in a formula?------------------------------Nicole Varner------------------------------

nicolevarner · Answer

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.

------------------------------
Nicole Varner
------------------------------

nicolevarner · Answer

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.------------------------------Nicole Varner------------------------------

austink · Answer

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.

Forum Discussion

Duration between two dates excluding weekends by hours/mins instead of days

Related Content

Date Duration Exclude Weekend

Duration Fields with Pipelines

Create a formula duration over 5 years

Duration Formula

Formula to exclude calculation