comparing time value

[ml]

I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a

4/1/2008 1:06:00PM --- this should yield to "7a-3p" shift
4/1/2008 1:13:00AM --- "11p-7a"
4/1/2008 7:04:00PM --- "3p-11p"
4/1/2008 7:00:00AM --- "11p-7a"


I wanted to assign above shift value to the time listed above so that
accordingly. The data I received didn't have the time listed as military
time.

This is what I've written, but it didn't return the right value. Help
please!!!

=IF(B2>=" 3:00:00PM","7a-3p",IF(B2<="
7:00:00AM","7a-3p",IF(B2>="11:00:00PM","3p-11p",IF(B2<="
3:00:00PM","3p-11p","11p-7a"))))

 

[Bob Phillips]

=IF(OR(MOD(B2,1)>=--"23:00",MOD(B2,1)<--"7:00"),"11p-7a",IF(MOD(B2,1)>=--"15:00","3p-11p",IF(MOD(B2,1)>=--"7:00","7a-3p")))

 

[T. Valko]

Question:

I have 3 working shifts...7a-3p, 3p-11p, and 11p-7a

4/1/2008 7:00:00AM --- "11p-7a"

How do you know that is the 11-7 shift and not the 7-3 shift?

I think you need to adjust your times so that they don't overlap:

7:00 AM to 2:59:59 PM

Or

7:00:01 AM to 3:00 PM

 

[ml]

Thanks Bob, I tried it but it gave me the "#VALUE!" and I entered it as an
array function. The problem is the time wasn't entered as military time. It
either AM or PM in the time format. I know it looks funny, but for all the
ones between 7-9 (single digit), it had a space infront of it.

 

[Bob Phillips]

It doesn't matter as long as it is real time, and no need to array enter it.

I got the results that you predicted with those dates.