Networkdays formula

[RattlingCarp3048]

=NETWORKDAYS(I3,J3,Lookup!$D$2:$D$4)-1-MOD(I3,1)+MOD(J3,1)

I have 2 columns with date/time stamps and need to caclulate the difference between them minus holidays and weekends but also factor in the time. if i only use the networkdays by default it rounds to the whole day and does not factor in the time. I found the above formula somewhere online. It seems to work pretty well for the vast majority of the rows, however, i have a few that are returning a negative value and im not sure why. Only commonality i can find is that all of the Order Entry dates happen to fall on a saturday.

But why would that return a negative difference? Shouldnt it still be positive? is there another factor i should include in the formula?

Collection Date/Time	Order Entry Date/Time	Coll. To OE (DAYS)
2/3/23 10:20 AM	2/4/23 3:56 AM	-0.27

2/17/23 4:57 PM	2/18/23 12:28 AM	-0.69
2/24/23 6:12 PM	2/25/23 2:53 AM	-0.64
6/16/23 4:09 PM	6/17/23 12:37 AM	-0.65
6/16/23 4:06 PM	6/17/23 12:41 AM	-0.64
6/23/23 5:13 PM	6/24/23 12:29 AM	-0.70
6/23/23 4:04 PM	6/24/23 12:46 AM	-0.64

 

[JamesCanale]

So networkdays, will return a 2 for 2 adjacent non-weekend days. And a two for a Friday-Monday. But it only returns a 1 for friday to saturday. Just one work day.

So you are subtracting 1 from 1 (instead of the usual 2). Then you are dealing with the time. So the time in the first day (Column I) is later than the time in the second day (J), so it'll finish negative.

The question might be, if networkdays is good to use, why are there activities that happen on Saturdays? What are the work days? (Networkdays.intl handles variability in the weekends.)

 

[RattlingCarp3048]

So networkdays, will return a 2 for 2 adjacent non-weekend days. And a two for a Friday-Monday. But it only returns a 1 for friday to saturday. Just one work day.

So you are subtracting 1 from 1 (instead of the usual 2). Then you are dealing with the time. So the time in the first day (Column I) is later than the time in the second day (J), so it'll finish negative.

The question might be, if networkdays is good to use, why are there activities that happen on Saturdays? What are the work days? (Networkdays.intl handles variability in the weekends.)

oh ok, that makes sense. so we have staff that work on the weekends and holidays, however, our leadership only works M-F, no weekends or holidays. so when we are trying to calculate the leaderships turnaround time we need to exclude weekends and holidays becasue they are off but need to include the time we are more precise. Can you think of a better formula that would work?