differentiate the delay in to 3 types

[vinu]

Hi all,

I need a help and I'm sure will get it here...

I'm preparing an OnTime report and in that I want to differentiate the
delay in to 3 types

Below is the scenario..

Shift time is in Column A, Log in time in Column B, Status in Column C

If the Shift time -6.30pm

Login time -6.30 or less then 6.30 -in the status cell "0"

Login time -with in 15mins -in the status cell "1"

Login time -with in 16-20mins -in the status cell "2"

Login time -with in 21-30mins -in the status cell "3"

Login time -Beyond 30min -in the status cell "4"



Thanks in anticipations...

Vinu

 

[Bob Phillips]

Try

=LOOKUP(MAX(0,ROUND((B2-A2)*24*60,0)),{0,1,15,20,30,999},{0,1,2,3,4})

 

[vinu]

Bob,

Thanks a ton for your quick help...

I'm getting the desired data with very little changes in the formula..

Thanks,
Vinu

 

[Bob Phillips]

Yeah,I thought the thresholds might need tweaking.