calculating am or pm shift
 

Ive been asked to create a formula to tell whether a single cells time is either AM or PM shift. Our AM shift is 05:45 to 15:45 while our PM shift is 15:46 to 01:15 the next day.

B3 is formatted to: m/d/yyyy h:mm and contains 1/2/2019 3:50:19 PM

Ive tried =IF(AND(B3=TIMEVALUE("05:45:00"),B3<=TIMEVALUE("1 5:45:00")),"AM","PM")

While the results show PM and this is correct, the cell above it contains 1/02/2019 3:13:24 and should result in AM €¦ but it results in PM as well.

What am I doing wrong?

calculating am or pm shift
 

Hi Dale,

Am Wed, 5 Jun 2019 08:57:00 -0700 (PDT) schrieb :

I?ve been asked to create a formula to tell whether a single cell?s time is either AM or PM shift. Our AM shift is 05:45 to 15:45 while our PM shift is 15:46 to 01:15 the next day.

B3 is formatted to: m/d/yyyy h:mm and contains 1/2/2019 3:50:19 PM

I?ve tried =IF(AND(B3=TIMEVALUE("05:45:00"),B3<=TIMEVALUE("1 5:45:00")),"AM","PM")

you can't compare date and time with a time value.
You must calculate the time from this timestamp:
=IF(AND(MOD(B3,1)=TIME(5,45,),MOD(B3,1)<=TIME(15, 45,0)),"AM","PM")



Regards
Claus B.
--
Windows10
Office 2016