Help with NETWORKDAYS formula

[gkisystems]

Column G is the "date/time in" and Column H is the "date/time out." In column I, I have "Turnaround Days" which is just H-G.


For example:


G2 = 03/06/2013 9:00AM
H2 = 03/08/2013 3:00PM
I2 = 2.25


How do I edit my formula in I2 to only calculate workdays? I want any time passed over the weekend (and holidays if possible) to not count. Additionally, if the date/time in or out occurs on the weekend, move it forward as if it were 8:00am on the next workday (only as it pertains to the calculation in cell I2...don't actually override the date/time in G2 and H2).


For example, if the date/time in is 3/9 @ 2:00PM and the date/time out is 3/16 @ 4:00PM, I would like my turnaround days to show up as 5.00.

 

[dermie_72]

I2 = networkdays(G2,H2)

 

[gkisystems]

I2 = networkdays(G2,H2)

Unfortunately, this formula will not work. In my example, it results in 3.00 days when I want it to say 2.25 days.

 

[dermie_72]

In I2 use this formula
=H2-G2

in J2 use this formula
=IF(NETWORKDAYS (G2 , H2) < ROUNDDOWN (I2,0),NETWORKDAYS(G2,H2)&RIGHT(I2,FIND(".",I2,1)+1),I2)<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)

See if that works. if you combine the two you may be able to get it all into one cell but ill leave that to you

I had to add some spaces in the formula for it to appear here. Remove them in excel to make it work</rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)

 

[gkisystems]

<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)
It doesn't seem to work.

G = 3/9/13 9:00 AM
H = 3/16/13 4:00 PM
I = H-G = 7.08 days
J = 5,848 days

Would like J = 5.00 days


</rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)<rounddown(i2,0),networkdays(g2,h2)&right(i2,find(".",i2,1)+1),i2)

 

[barry houdini]

Try this formula in I2 to get the required results for your examples

=NETWORKDAYS(G2,H2)+NETWORKDAYS(H2,H2)*(MOD(H2,1)-1)-NETWORKDAYS(G2,G2)*MOD(G2,1)

 

[dermie_72]

if you change I2 to = =ROUNDDOWN(H2-G2,2) then it should work. The issue was the decimals in I2. If you limit it, it will work. If you increase the number of decimals, then you will need to increase the last + in the formulas as well.