comparing time value
 

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"))))

comparing time value
 

=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")))

--
__________________________________
HTH

Bob

"ml" wrote in message
...
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"))))

comparing time value
 

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


--
Biff
Microsoft Excel MVP


"ml" wrote in message
...
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"))))

comparing time value
 

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" wrote:

=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")))

--
__________________________________
HTH

Bob

"ml" wrote in message
...
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"))))

comparing time value
 

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.

--
__________________________________
HTH

Bob

"ml" wrote in message
...
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" wrote:

=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")))

--
__________________________________
HTH

Bob

"ml" wrote in message
...
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"))))