Subtracting nights/weekends from workdays

%3CLINGO-SUB%20id%3D%22lingo-sub-1502217%22%20slang%3D%22en-US%22%3ESubtracting%20nights%2Fweekends%20from%20workdays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502217%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20situation%20where%20I%20am%20looking%20for%20help%20and%20guidance%20as%20I'm%20still%20new%20to%20Excel%20and%20formulas.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20I%20am%20trying%20to%20track%20when%20a%20case%20was%20open%20from%20a%20certain%20cut-off%20date%2Ftime%2C%20find%20out%20how%20many%20days%20it%20has%20been%20'untouched'%20from%20the%20specified%20%22Cut%20Off%22%20date%2Ftime%2C%20and%20then%20also%20subtract%20night%20and%20weekend%20hours%20to%20determine%20how%20many%20actual%20work%20hours%20has%20the%20case%20been%20opened%3F%26nbsp%3B%20(See%20screen%20shot)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture1.JPG%22%20style%3D%22width%3A%20743px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202730iDA58400D58931E31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture1.JPG%22%20alt%3D%22Capture1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20E%20-%20is%20feeding%20directly%20from%20Salesforce%2C%20so%20that%20just%20needs%20a%20refresh...%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20%3D%20formula%20is%3A%26nbsp%3B%3DCONCATENATE(TEXT(TODAY()%2C%22m%2Fdd%2Fyyyy%22)%26amp%3B%22%203%3A00%20PM%22)%20-%20So%20that%20it%20always%20shows%20the%20current%20days%20date%2C%20but%20also%20shows%20the%20cut-off%20time%20of%203%3A00%20PM%20(i.e.%207%2F1%2F2020%203%3A00%20PM)%3C%2FP%3E%3CP%3EColumn%20G%20%3D%26nbsp%3B%3DIF(D2%20%3D%20%22New%22%2CINT(%24F2-%24E2)%26amp%3B%22%20days%20%22%26amp%3BTEXT(%24F2-%24E2%2C%22h%22%22%20hrs%20%22%22m%22%22%20mins%20%22%22%22))%20-%20Is%20showing%20the%20time%20between%20when%20the%20case%20was%20opened%2C%20and%20the%20cut%20off%20date%2Ftime%20in%20%22Days%2C%20Hours%2C%20Minutes%22%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20H%20%3D%26nbsp%3B%3DIF(D2%3D%22NEW%22%2C(NETWORKDAYS(E2%2CF2)-1)*(%2219%3A00%22-%2207%3A00%22)%2BIF(NETWORKDAYS(F2%2CF2)%2CMEDIAN(MOD(F2%2C1)%2C%2219%3A00%22%2C%2207%3A00%22)%2C%2219%3A00%22)-MEDIAN(NETWORKDAYS(E2%2CE2)*MOD(E2%2C1)%2C%2219%3A00%22%2C%2207%3A00%22))%26nbsp%3B%20-%20Is%20what%20I%20currently%20have%20now%20in%20place%20to%20calculate%20the%20nights%20%2F%20weekends%20as%20well%20as%20taking%20into%20account%20our%20business%20hours%20of%207%3A00am%20-%207%3A00pm%2C%20but%20don't%20know%20if%20that%20is%20right%20since%20the%20time%20that%20is%20calculated%20is%20coming%20up%20for%20the%20first%20cell%20is%2023%3A20%3A00%20and%20that%20doesn't%20seem%20right%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20I%20am%20looking%20for%20is%20to%20figure%20out%20a%20way%20to%20subtract%20the%20time%20from%20Nights%20%2F%20Weekends%20(if%20that%20formula%20is%20correct)%20from%20Workdays%20to%20figure%20out%20exactly%20how%20much%20time%20(days%2Fhours%2Fminutes)%20a%20case%20was%20available%20to%20be%20worked%20since%20creation%20based%20on%20the%20date%20created%20and%20our%20cut-off%20day%2Ftime%20based%20on%20our%20business%20hours%20(7am%20-%207pm)%20and%20holidays%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...%20Phew...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20anyone%20out%20there%20has%20any%20guidance%20or%20thoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1502217%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504945%22%20slang%3D%22en-US%22%3ERe%3A%20Subtracting%20nights%2Fweekends%20from%20workdays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606460%22%20target%3D%22_blank%22%3E%40NeilKloster%3C%2FA%3E%26nbsp%3BPlease%20check%20the%20attached%20workbook.%20I%20break%20the%20calculation%20into%20steps%20to%20make%20it%20easier%20to%20understand.%20The%20last%20column%20is%20the%20single%20formula%20combining%20all%20the%20steps%20in%20previous%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1505571%22%20slang%3D%22en-US%22%3ERe%3A%20Subtracting%20nights%2Fweekends%20from%20workdays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1505571%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20helps%20-%20I%20might%20need%20to%20adjust%201-2%20things%2C%20but%20this%20definitely%20gets%20me%20on%20the%20right%20track.%26nbsp%3B%20Any%20thoughts%20on%20how%20to%20account%20for%20holidays%20at%20all%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeil%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507684%22%20slang%3D%22en-US%22%3ERe%3A%20Subtracting%20nights%2Fweekends%20from%20workdays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606460%22%20target%3D%22_blank%22%3E%40NeilKloster%3C%2FA%3E%26nbsp%3BIf%20you%20want%20to%20exclude%20also%20holidays%2C%20revise%20the%20Networkday.INTL%20formula%20in%20column%20Workday%20hours%20to%20incorporate%20the%20range%20of%20your%20holidays%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

 

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)

 

Capture1.JPG

 

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

@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.

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  

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