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

Try

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

--

HTH

Bob

"vinu" <(E-Mail Removed)> wrote in message
news:d5b656b5-4a13-4c02-930b-(E-Mail Removed)...
> 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,

Thanks a ton for your quick help...

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

Thanks,
Vinu

Yeah,I thought the thresholds might need tweaking.

--

HTH

Bob

"vinu" <(E-Mail Removed)> wrote in message
news:4e58965a-8a18-4c9d-9972-(E-Mail Removed)...
> Bob,
>
> Thanks a ton for your quick help...
>
> I'm getting the desired data with very little changes in the formula..
>
> Thanks,
> Vinu
>
>