I have used the following formula to find the no. of working hours overrun
between a predicted endtime and an actual end time. All of the answers that
it finds are positive even if the project finished early. How can i adjust
this formula to give negative answers as well as positive?

=IF(AND(INT(P38)=INT(O38),NOT(ISNA(MATCH(INT(P38), $Q$1:$Q$3,0)))),0,ABS(IF(INT(P38)=INT(O38),ROUND(2 4*(O38-P38),2),
(24*($P$2-$P$1)*
(MAX(NETWORKDAYS(P38+1,O38-1,$Q$1:$Q$3),0)+
INT(24*(((O38-INT(O38))-
(P38-INT(P38)))+($P$2-$P$1))/(24*($P$2-$P$1))))+
MOD(ROUND(((24*(O38-INT(O38)))-24*$P$1)+
(24*$P$2-(24*(P38-INT(P38)))),2),
ROUND((24*($P$2-$P$1)),2))))))

A bit of detail of what the cells hold might help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"DaveAsh" wrote in message
...
I have used the following formula to find the no. of working hours overrun
between a predicted endtime and an actual end time. All of the answers
that
it finds are positive even if the project finished early. How can i adjust
this formula to give negative answers as well as positive?

=IF(AND(INT(P38)=INT(O38),NOT(ISNA(MATCH(INT(P38), $Q$1:$Q$3,0)))),0,ABS(IF(INT(P38)=INT(O38),ROUND(2 4*(O38-P38),2),
(24*($P$2-$P$1)*
(MAX(NETWORKDAYS(P38+1,O38-1,$Q$1:$Q$3),0)+
INT(24*(((O38-INT(O38))-
(P38-INT(P38)))+($P$2-$P$1))/(24*($P$2-$P$1))))+
MOD(ROUND(((24*(O38-INT(O38)))-24*$P$1)+
(24*$P$2-(24*(P38-INT(P38)))),2),
ROUND((24*($P$2-$P$1)),2))))))

Sorry,
O38 is the projected completion date/time
P38 is the actual date/time completed
P1 is the day start time (09:00)
P2 is the day end time (17:00)

"Bob Phillips" wrote:

A bit of detail of what the cells hold might help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"DaveAsh" wrote in message
...
I have used the following formula to find the no. of working hours overrun
between a predicted endtime and an actual end time. All of the answers
that
it finds are positive even if the project finished early. How can i adjust
this formula to give negative answers as well as positive?

=IF(AND(INT(P38)=INT(O38),NOT(ISNA(MATCH(INT(P38), $Q$1:$Q$3,0)))),0,ABS(IF(INT(P38)=INT(O38),ROUND(2 4*(O38-P38),2),
(24*($P$2-$P$1)*
(MAX(NETWORKDAYS(P38+1,O38-1,$Q$1:$Q$3),0)+
INT(24*(((O38-INT(O38))-
(P38-INT(P38)))+($P$2-$P$1))/(24*($P$2-$P$1))))+
MOD(ROUND(((24*(O38-INT(O38)))-24*$P$1)+
(24*$P$2-(24*(P38-INT(P38)))),2),
ROUND((24*($P$2-$P$1)),2))))))