Conditional Statements and Time Format

[robofanuc]

Hello,

I have a spreadsheet where I need to calculate a range of time in a
cell and display a value in another cell.

Example is in cell F2 I have a time displayed of 15:34, and in the
calculation window it displays as 3:34:00 PM. In Cell F3 I want to
display one of three things, "1st shift", "2nd shift" or "3rd shift".
Is it possible to use the conditional statements to give me the value
of "1st shift" when cell F2 is between >= 07:00 and < 15:00?

I can't find anything that speaks to getting thee range information
from time.

Thanks,
Brian

 

[Bob Umlas]

=IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","")

 

[Guest]

Try something like this:

With
F2: (a time value)

This formula returns the corresponding shift
F3: =CHOOSE(INT(MOD(A1-7/24,1)*3)+1,"1st","2nd","3rd")&" shift"

Where:
Shift 1 is from 11 PM (inclusive) to 7 AM (exclusive)
Shift 2 is from 7 AM (inclusive) to 3 PM (exclusive)
Shift 3 is from 3 PM (inclusive) to 11 PM (exclusive)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ummmm....typo (sorry)

The formula should refer to F2 (NOT A1)
F3: =CHOOSE(INT(MOD(F2-7/24,1)*3)+1,"1st","2nd","3rd")&" shift"

***********
Regards,
Ron

XL2002, WinXP

 

[diablo]

Works great Ron, thanks.

Brian (at home address)

--

 

[diablo]

This works too, thanks.

Brian

--

=IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","")