View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default 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