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.
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?
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?
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!
- I 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.