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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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


"(E-Mail Removed)" wrote:

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

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


"Ron Coderre" wrote:

> 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
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >

Works great Ron, thanks.

Brian (at home address)

--

"Ron Coderre" <(E-Mail Removed)> wrote in message
news:E036B063-B0D4-463C-865E-(E-Mail Removed)...
> 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
>
>
> "Ron Coderre" wrote:
>
>> 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
>>
>>
>> "(E-Mail Removed)" wrote:
>>
>> > 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
>> >
>> >

This works too, thanks.

Brian

--

"Bob Umlas" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","")
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>