Thanks Skaria I have used a formulae like the below but get the wrong result
when (E20-D20) is greater than say 10hrs, please help

=IF(A51<=0,4,LOOKUP(TEXT(E20-D20,"h"),{"0","1","2","3"},{4,3,2,1}))

"Jacob Skaria" wrote:

Dear Graham

The below formula do not use the helper cell.

=LOOKUP(VALUE(TEXT(F1-E1,"hh")),{0,1,2,3,4},{"100% Planned",3,2,1,4})

Can you clarify the first condition (planned =4). I am not sure....But
still you should be able to modify the values from within the lookup and
result array....

If this post helps click Yes
---------------
Jacob Skaria


"Graham" wrote:

Hi all I have the following criteria:
planned = 4; 1 hour above planned = 3; 2 hours above planned = 2; 3 hours above planned = 1
I need a formulae to satisfy the above requirements (i have a helper cell
"a51"
E1=PLANNED DATE TIME (dd-mm-yy HH:mm)
F1=ACTUAL DATE TIME
F1-E1=HELPER CELL(A51)
REMEMBER IF A51=O THEN THAT IS 100% PLANNED

THANKX
GEES