Thread
:
calculating am or pm shift
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
Posts: 3,872
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
Reply With Quote
Claus Busch
View Public Profile
Find all posts by Claus Busch