Forum Discussion
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...
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.
I think this is same as another question:
https://techcommunity.microsoft.com/t5/excel/networkdays-formula/m-p/3693884
here is my answer there:
= NETWORKDAYS(C2,D2) - 1 + MOD(D2,1) - MOD(C2,1)
and format cells as [h]:mm:ss as you noted.
but again this doesn't take into account any work hours and doesn't account for any start or end dates that are on the weekend.
If the start or end might be a weekend then try:
= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2) - NETWORKDAYS(D2,D2) + 1 + MOD(D2,1) - MOD(C2,1)
EDIT after thinking about this if it starts or end on a weekend then the MOD parts (the hours) shouldn't count either so:
= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2)*(1+MOD(C2,1)) - NETWORKDAYS(D2,D2)*(1- MOD(D2,1) ) + 1
Thank you for replying.
Sorry, I didn't even think to include start-end times.
lower / upper ranges for the start of the workday would be 7:00 AM and the end would be 11:00 PM.
- so should 2022-12-17 10:00PM to 2022-12-18 10:00AM be only 4 hours?
what about starting at 2022-12-17 11:30PM? would that be 3 hours?- Just to clarify:
So from 12/17/22 10:00 PM to 12/18/22 10:00 AM would be 4 hours correct.
Our business hours are from 7am - 11pm M-F, so we would not operate at 11:30 PM. If something got sent to us after business hours (not usual, but does happen), at say 11:30 PM on a weekday, I would have it's start time at 7:00 AM the next business day (since no one would be available to process it until that time).
All I'm looking for sadly is the best of both worlds where I want the Networkdays function to calculate the difference between two dates/times (excluding weekends), but I need it in hours, not days.
Does that help?
Thank you for your support!
