HELP NOTHING WORKS

D

DaveAsh

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(24*(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)
 
M

Mike H

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
 
D

DaveAsh

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

Mike H said:
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 said:
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(24*(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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top