Forum Discussion

Brass Contributor

Dec 28, 2022

Solved

calculate total hours between date/time (excluding weekends)

Hello, I know that this is pretty easy for this group, but it's escaping me at the moment. I want to calculate the hours between two fields, but I just want a simple number of hours. No concat...

excel

Formulas and Functions

mtarler
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.
SLA Example.xlsx32 KB

NeilKloster

Brass Contributor

Jan 18, 2023

mtarler

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?

mtarler

Silver Contributor

Jan 18, 2023

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.

NeilKloster
Brass Contributor
Jan 19, 2023
mtarler

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!