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))))))

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)

I may be missing something but what do the start and end times add to your
formula? you seem to be simply calculating the difference between O38 and P38.

If you want to do that and display negaive values then you have 2 choices.
Use the 1904 date system which could mess up other dates on your sheet or try
this:-

=IF(P38=O38,TEXT(P38-O38,"[h]"),TEXT(O38-P38,"-[h]"))

Mike

"DaveAsh" wrote:

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))))))

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)

The formula only gives total hour difference and not 9am-5pm working hours
difference.

"Mike H" wrote:

I may be missing something but what do the start and end times add to your
formula? you seem to be simply calculating the difference between O38 and P38.

If you want to do that and display negaive values then you have 2 choices.
Use the 1904 date system which could mess up other dates on your sheet or try
this:-

=IF(P38=O38,TEXT(P38-O38,"[h]"),TEXT(O38-P38,"-[h]"))

Mike

"DaveAsh" wrote:

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))))))

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)