Forum Discussion
calculate total hours between date/time (excluding weekends)
- Dec 29, 2022
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 basically you need to 'make' anything after 7PM the next day so simply add 5/24 to force the any time after 7pm to be the next day (the morning hours becoming later that same day don't matter). So simply =NETWORKDAYS(start, end+5/24, holidays)-1 will work ....... ALMOST. The problem will come in when it starts on Th and end after 7pm Fri since that would become Sat and NETWORKDAYS will ignore Sat (or any of the holidays). So if we assume we don't care what day it is, if it is after 7pm then +1 workday then maybe:
=NETWORKDAYS(start, end, holidays) -1 + (MOD(end,1)>19/24)
Sort of - so basically it would be the next business day, so if it comes in after Thursday at 7 PM, it is considered Friday's business day. If it comes in after Friday at 7 PM, then it would be considered Monday's business day (unless it's a holiday).
(So many conditions). Phew.
To summarize:
6 AM - 7 PM (M-F) Business Hours.
If it comes in between 6 AM - 7 PM on a Monday, then the team has until 7 PM the next business day (Tuesday) to complete it. If it comes in after 7 PM on a Monday, then it is considered the next business day (Tuesday) and the team has until Weds at 7 PM to complete it. Does that help?
- NeilKlosterJan 19, 2023Brass Contributor
Think I got it all setup (hopefully unless they come back with changes). Once again, I really, REALLY want to thank you for all your help! I really appreciate it greatly!
- NeilKlosterJan 19, 2023Brass Contributor
So just looking that file that you sent back, I ran through a few scenarios with weekends and it looks like it works correctly. I ran through it with just the normal work weeks and also tried it out on a holiday and it seems to work correctly. But it sounded like you thought that there was another issue?
- mtarlerJan 19, 2023Silver ContributorI think the only concern I might have is weekend/holiday issues like coming in and going out the same weekend may result in negative days or coming in on a Th and going out on Sat (before 7pm) may say 1 day. But again if no one is working on the weekend then its ok. but also holidays like columbus day or others that maybe are considered 'holiday' in terms of shipping times but people may still be working. Also didn't account for receiving after 7pm so you need to add -(MOD(start,1)>19/24).
So again if we need to account for every contingency it can get dicey but otherwise it should work.