How to find 3 shifts(Morning, afternoon and Night) from Time data in excel

[Sundish]

Morning work hrs: 6:00 am to 2:00pm (Day1)

Afternoon work hrs: 2:00 pm to 10:00pm (Day1)

Night Shift Work hrs: 10:00 pm to 6:00am (Day1+Day2)=Day1 ie: Night work hrs includes the next day but I want to consider as the previous day

Please help!!   @Riny_van_Eekelen  

@Subodh_Tiwari_sktneer 

[#2]

@Sundish Perhaps the example in the attached workbooks helps you find a solution for your real life schedule.

[#3]

Hi

I looked at the example below and cant see how it helps?  I need to write the right If statement to convert these times into morning <12pm, afternoon 12-4 and evening 4-10pm I tried this =IF(B2<12,"M",IF(B2<16,"A","E"))

@Riny_van_Eekelen 

[#4 · Accepted solution]

@Citychick The original post was about how to include a shift that goes past midnight as a shift of the previous day. In your case, first make sure that you have the times entered as real time values. Not a text that looks like a time. Then, be aware that Excel stores time as a number representing the part of the day that has passed. So 8 am = 0.333333333 (i.e. one third into a 24hr day). Thus, when calculating with time, use such number representations or better use the TIME function.

Let's say B2 contains the time value for 9 am (should show in the formula bar as 09:00:00 you can use this formula to find the shift code:

=IF(B2<TIME(12,0,0),"M",IF(B2<TIME(16,0,0),"A","E"))

[#5]

Thank you so much

@Riny_van_Eekelen