time grouping

New Contributor

Hi,

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

@anags604 

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

In another cell in row 2, enter the formula

 

=IF(AND(HOUR(D2)>=7,HOUR(D2)<19),"AM","PM")

 

Fill down.

@Hans Vogelaar 

It worked, thank you very much.