Forum Discussion
How to find 3 shifts(Morning, afternoon and Night) from Time data in excel
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"))
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"))
Thank you so much