L
LJ
All,
I am trying to combine NETWORKDAYS with a formula that calculates a
total time to complete a task minus the date and time an issue has
been in a pending status. I need the formula to factor-out weekends,
the date and time an issue is in pending status and work hours (7AM to
6PM during the week). I keep getting a negative time or incorrect
value. Please help.
For example:
D9 = Date and Time Issue went into Pending Status (this field is
sometimes blank)
C9 = Start Date and Time = 7/30/2010 13:24
B9 = End Date and Time = 8/2/2010 8:20
This is the formula string I have been using:
=IF(ISBLANK(D9),((NETWORKDAYS(C9,B9)*13/24)-C9+B9),
((NETWORKDAYS(C9,D9)-1)*13/24)-C9+B9)
I am trying to combine NETWORKDAYS with a formula that calculates a
total time to complete a task minus the date and time an issue has
been in a pending status. I need the formula to factor-out weekends,
the date and time an issue is in pending status and work hours (7AM to
6PM during the week). I keep getting a negative time or incorrect
value. Please help.
For example:
D9 = Date and Time Issue went into Pending Status (this field is
sometimes blank)
C9 = Start Date and Time = 7/30/2010 13:24
B9 = End Date and Time = 8/2/2010 8:20
This is the formula string I have been using:
=IF(ISBLANK(D9),((NETWORKDAYS(C9,B9)*13/24)-C9+B9),
((NETWORKDAYS(C9,D9)-1)*13/24)-C9+B9)