Subtracting nights/weekends from workdays

Occasional Contributor

Subtracting nights/weekends from workdays

Hello,

I have a situation where I am looking for help and guidance as I'm still new to Excel and formulas.

I have a spreadsheet where I am trying to track when a case was open from a certain cut-off date/time, find out how many days it has been 'untouched' from the specified "Cut Off" date/time, and then also subtract night and weekend hours to determine how many actual work hours has the case been opened?  (See screen shot)

Column E - is feeding directly from Salesforce, so that just needs a refresh...

Column F = formula is: =CONCATENATE(TEXT(TODAY(),"m/dd/yyyy")&" 3:00 PM") - So that it always shows the current days date, but also shows the cut-off time of 3:00 PM (i.e. 7/1/2020 3:00 PM)

Column G = =IF(D2 = "New",INT(\$F2-\$E2)&" days "&TEXT(\$F2-\$E2,"h"" hrs ""m"" mins """)) - Is showing the time between when the case was opened, and the cut off date/time in "Days, Hours, Minutes"

Column H = =IF(D2="NEW",(NETWORKDAYS(E2,F2)-1)*("19:00"-"07:00")+IF(NETWORKDAYS(F2,F2),MEDIAN(MOD(F2,1),"19:00","07:00"),"19:00")-MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1),"19:00","07:00"))  - Is what I currently have now in place to calculate the nights / weekends as well as taking into account our business hours of 7:00am - 7:00pm, but don't know if that is right since the time that is calculated is coming up for the first cell is 23:20:00 and that doesn't seem right?

So what I am looking for is to figure out a way to subtract the time from Nights / Weekends (if that formula is correct) from Workdays to figure out exactly how much time (days/hours/minutes) a case was available to be worked since creation based on the date created and our cut-off day/time based on our business hours (7am - 7pm) and holidays?

... Phew...

Hoping anyone out there has any guidance or thoughts?

Thank you!

3 Replies

Re: Subtracting nights/weekends from workdays

@NeilKloster Please check the attached workbook. I break the calculation into steps to make it easier to understand. The last column is the single formula combining all the steps in previous columns.

Re: Subtracting nights/weekends from workdays

Thank you so much !@hynguyen

This helps - I might need to adjust 1-2 things, but this definitely gets me on the right track.  Any thoughts on how to account for holidays at all?

Neil

Re: Subtracting nights/weekends from workdays

@NeilKloster If you want to exclude also holidays, revise the Networkday.INTL formula in column Workday hours to incorporate the range of your holidays