Forum Discussion
Solved
How to find 3 shifts(Morning, afternoon and Night) from Time data in excel
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
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"))
Sundish Perhaps the example in the attached workbooks helps you find a solution for your real life schedule.
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"))
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"))
