Need Help - If and Then

[arajan60]

Hello Team,

I would require help with the If and Then formula for the attached sheet.

Regard

[JohnTopley]

Please check your shift times (am/pm) as they do not appear correct to me.

11am-12pm Afternoon
12am-1am Afternoon

8am-9am Night
9pm-10pm Night

[arajan60]

Hello John- Thanks for your quick response. You are right that was a mistake on my part. FYI... We are working in a 24 hr rotation **** and we work in 3 shifts.

I have updated the attachment on my first post (since I am new on this forum not able to attach here)

Have a look at my original post and please help me with a formula of (if and then)

Regards,
Abhilash

[JeteMc]

This proposal employs two helper columns (O:P) which may be moved and or hidden for aesthetic purposes.
Starting with cell O4, column O is populated using: =MOD(SUM(O3,1/24),1) Note that the value 6:30 AM is typed into cell O3
Column P is also populated using: =MOD(SUM(O3,1/24),1)
The Formula Time Slot column is populated using:

Please Login or Register  to view this content.

The Formula Shift column is populated using: =INDEX(L$3:L$26,MATCH(H3,M$3:M$26,0))
Let us know if you have any questions.

[arajan60]

Thank you for the support. Appreciate your help.

[JeteMc]

You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.