Forum Discussion
calculate total hours between date/time (excluding weekends)
NeilKloster So there are 2 things happening here:
a) any of the lines with start and end being the same day are 0 because you missed the last line of the equation
b) the numbers look weird because you didn't format them to show [h]:mm
If you want purely hours (i.e. [h]:mm of 1:30 would be 1.5) then just add 24* at the beginning (or *24 at the end) of the equation.
see attached.
Okay, first and foremost - you are awesome! The amount of effort you are putting into this to help me is amazing! TYSM!!
So I put in that amazing formula and I'm getting results, but they are off. I am including a simplified file example to show you what is going on. Please ignore the conditional format coloring on column C.
NeilKloster So there are 2 things happening here:
a) any of the lines with start and end being the same day are 0 because you missed the last line of the equation
b) the numbers look weird because you didn't format them to show [h]:mm
If you want purely hours (i.e. [h]:mm of 1:30 would be 1.5) then just add 24* at the beginning (or *24 at the end) of the equation.
see attached.
So now the client has come back and asked for holidays to also be excluded. I know that there is a way to do this, by creating a list of dates, but when I tried it, it didn't work for me. Any thoughts on how to include this as well?
NeilKloster you should only need to add a list of holidays in the workbook and refer to that list in the NETWORKDAYS formulas. In the attached I added a sheet with a HOLIDAYS tab I created for one of my companies and you can 'turn on'/'turn off' different holidays and add years to the Year column and the corresponding holiday dates are calculated.
On Sheet1 is both my formulas (output in H:MM vs # hours) and the alternate formula from HansVogelaar and both have been updated with the holidays reference. Note there are a few output differences between the 2 so use whichever works for you.
Appreciate it BTW. @Sorry to ask again - the client came back this morning apologetic, with how they want to track these cases. The don't want business hours, they only want business days and they explained differently how they want this tracked. LOL!
So basically, if a case comes in from 6 AM - 7 PM (M-F), it needs to be completed in 1 business day, meaning no later than 7 PM the next business day. The way that I have everything setup now, if I change the time on the Date/Time closed column past 7 PM, everything stays the same.
I think I'm missing something simple, where basically I need to have a column that indicates that the value in the Date/Time Closed is past 7 PM, but I think I've looked at this time enough that I'm blind to it.
Example:
This is correct since the date/time closed is under 7 PM
This is not correct since the date/time closed is past 7PM
It should read 2.00 business days since the date/time closed is past the 7pm cut-off.
I tried doing even just a "-1" on the business days column, which would remove a day, but that doesn't ultimately do anything since I need the formula to be time sensitive to when it was closed. Adding an attachment sample - any help again is appreciated. Sorry for constantly asking.
- Thank you so much! I adjusted it as you stated and it works perfectly!! THANK YOU AGAIN SO MUCH!!