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!
