I believe there is a way to express a Day, Evening and Night shifts with Excel formula using Start time and End time. I have 3 standard shift types: “Day,” “Evening” and “Night.”
Day Shift information:
Start time: 7:00am
End time: 3:00pm
Evening Shift information:
Start time: 3:00pm
End time: 11:00pm
Night Shift information:
Start time: 11:00pm
End time: 6:59am
For example, if A2 = 7:00am and B2=3:00pm, C2 outcome = Day Shift. The goal is to have a formula that will take the start time (A2 = 7:00am) and the end time (B2=3:00pm) range and return “Day Shift” if A2>=7:00am and B2<=3:00pm. It should cover times that fall between 7:00am and 3:00pm as well (for example, 8:00am - 2:57 pm = Day Shift; 11:00am – 3:00pm = Day Shift).
If A2>=3:00pm and B2<=11:00pm, the formula should return “Evening Shift” It should cover times that fall between 3:00pm and 11:00pm as well (for example, 5:00pm – 8:00pm = Evening Shift; 8pm – 9:00pm = Evening Shift).
Lastly, if A2>=11:00pm and B2<=7:00am, the formula should return “Night Shift” It should cover times that fall between 11:00pm and 7:00am as well (for example, 12:00am – 5:00am = Night Shift; 11:50pm – 6:59am = Night Shift).
The goal is to have a formula that will combine the three scenarios and display “Day,” “Evening” and Night displayed based on the start and end time. Attached is a sample copy for your convenience.
Thank you in advance for your assistance.
Bookmarks