• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to calucalate standby for every 4 hrs.

P

PradeepN

New Member
Need to calculate standby for every 4 hrs.
<4 hrs is 0 block.
>=4-<=8 is 1 block
>=8-<=12 is 2 block
>=12-<=16 is 3 block
>=16-<=20 is 4 block
and so on.
I worked and for few the calculation is not perfect. Like for 16 it is calculating 3 block instead of 4
 

Attachments

Dear PradeepN

Your requirements includes the following two conditions

> =12-<=16 is 3 block
> =16-<=20 is 4 block

both evaluate to true when standby equals 16.

If you want 16 to evaluate to 4 then you'd need the conditions to be

> =12-<16 is 3 block
> =16-<=20 is 4 block

Extending this to avoid all such clashes gives you:

<4 hrs is 0 block.
> =4-<8 is 1 block
> =8-<12 is 2 block
> =12-<16 is 3 block
> =16-<20 is 4 block

so your formula in F2 would need to be:

=IF(E2="No","0",IF(E2<TIME(4,0,0),"0",IF(AND(E2>=TIME(4,0,0),E2<TIME(8,0,0)),"1",IF(AND(E2>=TIME(8,0,0),E2<TIME(12,0,0)),"2",IF(AND(E2>=TIME(12,0,0),E2<TIME(16,0,0)),"3",IF(AND(E2>=TIME(16,0,0),E2<TIME(20,0,0)),"4","5"))))))

A simpler formula is =IF(E2="No","0",int(E2*6))
 
Thanks a lot. One more help. Is my calculating time difference is correct.
=IF(OR(C2="Normal Shift",D2="Day Shift",C2="",D2="",C2="WO / Leave(s)",D2="WO / Leave(s)",C2="select",D2="Select"),"No",IF(C2>D2,1+D2,D2)-C2)
 
You must log in or register to reply here.
Back
Top