• 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.

I want a formula to determine the shift type

A

ashwithonline

Member
Hi,


This is the second time that I am coming back with a query...last time i had one...wherein my blogmates over here helped me a lot....thanks again

This time....

Basis the shift in and out times, i want to classify it as A, B and C type of shift. The condition is any shift that ends before 12 in the night is A, any shift that ends before 3.30 am is B and any shift that ends before 6.30 am is C.


Thanks for all your ...friends.

Jagdish
 
It would be tricky for 00:00. Should that be A or B?

If you want to consider 00:00 as A, the above formula would need some revision.
=IF(A2=TIME(0,0,0),"A",IF(A2<=TIME(3,30,0),"B",IF(A2<=TIME(6,30,0),"C","A")))

However if it B, that would definitely work.

If you have a full date instead of time in A2, then you could use A2-INT(A2) to extract the time portion of the full date.

Best,
AJ
 
Hi Friends,

Looks like I missed giving some information from my side.....so I have attached the file instead..the first sheet is from the operation with the input and the second sheet has the output....

the formula mentioned above solves one part of the problem, the second part stills remains of looking the concerned shift for a person and then basis the end time of the shift classifying the shift to A,B and C.

Thanks for all the help......
 

Attachments

ashwithonline said:
.......I have attached the file instead..the first sheet is from the operation with the input and the second sheet has the output...based the end time of the shift classifying the shift to A,B and C.......
Click to expand...

Just modified Hui's formula in Post #2 to suit your Operation input, and

in HR output sheet C2, formula copy across and down :

=IF(Operations!C2="Weekly Off","Weekly Off",IF(--RIGHT(Operations!C2,7)<=TIME(3,30,0),"B",IF(--RIGHT(Operations!C2,7)<=TIME(6,30,0),"C","A")))

Regards
Bosco
 

Attachments

Last edited:
@Bosco...Thanks a lot friend....

But if you could help me with one more adjustment.....

The formula that you have given works in a situation where the sequence is same in the Operations and HR tab..

But i need a formula which can tell the shift type irrespective of the sequence of the name..may be vlookup, match or index can help...just a wild guess....

Please find the attached sheet which can explain the scenario...

again thanks in advance,,,,
 

Attachments

ashwithonline said:
@Bosco...Thanks a lot friend....
But if you could help me with one more adjustment.....
again thanks in advance,,,,
Click to expand...

Try this revised....,

C2, formula copy across and down :

=IF(INDEX(Operations!C$2:C$15,MATCH($B2,Operations!$B$2:$B$15,0))="Weekly Off","Weekly Off",IF(--RIGHT(INDEX(Operations!C$2:C$15,MATCH($B2,Operations!$B$2:$B$15,0)),7)<=TIME(3,30,0),"B",IF(--RIGHT(INDEX(Operations!C$2:C$15,MATCH($B2,Operations!$B$2:$B$15,0)),7)<=TIME(6,30,0),"C","A")))

Regards
Bosco
 

Attachments

@Bosco.........

Thanks a lot Brother......

One more small favour....... to the above formula..a small modification is needed....

apart from Weekly off and A,B and C shift type there is another condition of Leave which is to be populated from the Operations sheet to HR....

and Thanks a lot again.......
 
ashwithonline said:
@Bosco.........

Thanks a lot Brother......

One more small favour....... to the above formula..a small modification is needed....

apart from Weekly off and A,B and C shift type there is another condition of Leave which is to be populated from the Operations sheet to HR....

and Thanks a lot again.......
Click to expand...

Then add another IF function.........

see attached file

Regards
 

Attachments

You must log in or register to reply here.
Back
Top