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