time grouping

New Contributor


I am kind of new to excel formulas. I am trying to figure out a formula for a cell to identify the shift time we received the call. 

My raw input looks like this: 

3/30/2021 12:20:01 PM


So if we received the call between 7:00:01 AM to 6:59:59 PM, I want the cell to call it 'AM shift' and

if we received the call between 7:00:00 PM to 6:59:59 AM, I want the cell to call it 'PM shift'.


Please help me out with this.

2 Replies


Let's say the date and time is in D2 and down.

In another cell in row 2, enter the formula




Fill down.

@Hans Vogelaar 

It worked, thank you very much.