Time Value Between Two Times

[Debbie]

Here is another scenario.

If F85 is greater than or equal to 8:30 am but less than or equal to
9:00 am, then I need it to return a 1, If it is not between 8:30 am
and 9:00 am then I need it to return "B". I have the cell formated as
time 00:00 AM. What am I doing wrong?

IF(AND(F85>=8:30,F85<=9:00),1,"B")

 

[Debbie]

Here is another scenario.

If F85 is greater than or equal to 8:30 am but less than or equal to
9:00 am, then I need it to return a 1, If it is not between 8:30 am
and 9:00 am then I need it to return "B". I have the cell formated as
time 00:00 AM. What am I doing wrong?

IF(AND(F85>=8:30,F85<=9:00),1,"B")

The other criteria that it needs to meet is that c86 needs to be
greater than 12 otherwise if less than 12 then "B"

 

[T. Valko]

IF(AND(F85>=8:30,F85<=9:00),1,"B")

Try it like this:

=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,0,0)),1,"B")

Or, use cells to hold the time boundaries...

A1 = 8:30 AM
B1 = 9:00 AM

=IF(AND(F85>=A1,F85<=B1),1,"B")

You probably want to format the result as General.

 

[Fred Smith]

1. First, your format needs to be: hh:mm AM/PM (Excel doesn't use zeros for
time format, because it wouldn't know whether you wanted hours and minutes
or minutes and seconds.)
2. Second, Excel doesn't recognize 8:30 as a time. Your choices are:
IF(AND(F85>=time(8,30,0),F85<=time(9,0,0),1,"B")
IF(AND(F85>=timevalue("8:30"),F85<=timevalue("9:00"),1,"B")
IF(AND(F85>=--"8:30",F85<=--"9:00"),1,"B")

Regards,
Fred

 

[Fred Smith]

Just add it to the And function:
IF(AND(F85>=--"8:30",F85<=--"9:00",C86>12),1,"B")

Regards,
Fred

Here is another scenario.

If F85 is greater than or equal to 8:30 am but less than or equal to
9:00 am, then I need it to return a 1, If it is not between 8:30 am
and 9:00 am then I need it to return "B". I have the cell formated as
time 00:00 AM. What am I doing wrong?

IF(AND(F85>=8:30,F85<=9:00),1,"B")

The other criteria that it needs to meet is that c86 needs to be
greater than 12 otherwise if less than 12 then "B"

 

[Ron Rosenfeld]

Here is another scenario.

If F85 is greater than or equal to 8:30 am but less than or equal to
9:00 am, then I need it to return a 1, If it is not between 8:30 am
and 9:00 am then I need it to return "B". I have the cell formated as
time 00:00 AM. What am I doing wrong?

IF(AND(F85>=8:30,F85<=9:00),1,"B")

It's always helpful if you explain "why" you think you are doing something
wrong. In other words, are you getting an error message? If so, what is it?

Are you getting a wrong answer? If so what is the input and output?

And so forth.

From the looks of your formula, it appears as if you are entering the Time
value incorrectly. In that case, you should be getting an error message "The
formula you typed contains an error".

If that is the case, then try this:


=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,,)),1,"B")

Or, put your times in a cell, and reference those cells in your formula.

--ron

 

[Ron Rosenfeld]

The other criteria that it needs to meet is that c86 needs to be
greater than 12 otherwise if less than 12 then "B"

What if C86 is EQUAL to 12?

so either:

=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,,),C86>12),1,"B")

or

=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,,),C86>=12),1,"B")

--ron

 

[Debbie]

Try it like this:

=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,0,0)),1,"B")

Or, use cells to hold the time boundaries...

A1 = 8:30 AM
B1 = 9:00 AM

=IF(AND(F85>=A1,F85<=B1),1,"B")

You probably want to format the result as General.

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thank you, works great!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try it like this:

=IF(AND(F85>=TIME(8,30,0),F85<=TIME(9,0,0)),1,"B")

Or, use cells to hold the time boundaries...

A1 = 8:30 AM
B1 = 9:00 AM

=IF(AND(F85>=A1,F85<=B1),1,"B")

You probably want to format the result as General.

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thank you, works great!