I need Time Range to return Value

[Eric Ocasio]

I need to get a Value Based on Start time.

If the Start time is between 7:00 and 14:59 = First
If the Start time is between 15:00 and 21:59 = Second
If the Start time is between 22:00 and 6:59 = Third
Values will show in Column "D"
A B C D
1 Name Start End Shift
2 John Doe 8:00 17:00 Value?

Any help.

 

[Claus Busch]

Hi Eric,

Am Fri, 18 Oct 2013 13:07:47 -0700 (PDT) schrieb Eric Ocasio:

A B C D
1 Name Start End Shift
2 John Doe 8:00 17:00 Value?

try in D2:
=IF(AND(B2>=TIME(7,,),B2<TIME(15,,)),"First",IF(AND(B2>=TIME(15,,),B2<TIME(22,,)),"Second","Third"))
and copy down


Regards
Claus B.

 

[Eric Ocasio]

Hi Claus

Thanks you so much, this really help. Only have one issue, on the blank cells it is giving me "Third" instead of a blank cell. Any ideas?

 

[Claus Busch]

Hi Eric,

Am Fri, 18 Oct 2013 13:28:30 -0700 (PDT) schrieb Eric Ocasio:

Thanks you so much, this really help. Only have one issue, on the blank cells it is giving me "Third" instead of a blank cell. Any ideas?

then try:
=IF(B2="","",IF(AND(B2>=TIME(7,,),B2<TIME(15,,)),"First",IF(AND(B2>=TIME(15,,),B2<TIME(22,,)),"Second","Third")))


Regards
Claus B.

 

[Eric Ocasio]

You are a pro my friend thank you so much.

Hi Eric,



Am Fri, 18 Oct 2013 13:28:30 -0700 (PDT) schrieb Eric Ocasio:






then try:

=IF(B2="","",IF(AND(B2>=TIME(7,,),B2<TIME(15,,)),"First",IF(AND(B2>=TIME(15,,),B2<TIME(22,,)),"Second","Third")))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2