IF statement to evaluate time data

[Daren]

I'm trying to use an IF statement for time based data. The data that I'm
evaluating is in the 0:05 time format, which is the same as [h]:mm format in
custom formatting under cell formats.

Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
if this value is greater than or equal to 1 and at the same time less than or
equal to 5. My current logic statement is set as:

=IF(1<=k1<=5, "1-5",false). This returns false.

I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results in an error.

Please help.

 

[Dave Patrick]

You might want to review this page.

http://www.cpearson.com/excel/datetime.htm#AddingTimes

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| I'm trying to use an IF statement for time based data. The data that I'm
| evaluating is in the 0:05 time format, which is the same as [h]:mm format
in
| custom formatting under cell formats.
|
| Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
| if this value is greater than or equal to 1 and at the same time less than
or
| equal to 5. My current logic statement is set as:
|
| =IF(1<=k1<=5, "1-5",false). This returns false.
|
| I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results in an
error.
|
| Please help.

 

[Bob Phillips]

=IF(AND(1<=K1,K1<=5),"1-5")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Daren]

Tried that but it returns it as false. This may have something to do with
the 0:05
formatting in the cell that I want to evaluate. That is, the 0:05 in k1 is
returned based on an IF statement itself. Might this affect the result of
getting false? Do you have other suggestions?

Thanks.

=IF(AND(1<=K1,K1<=5),"1-5")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I'm trying to use an IF statement for time based data. The data that I'm
evaluating is in the 0:05 time format, which is the same as [h]:mm format in
custom formatting under cell formats.

Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
if this value is greater than or equal to 1 and at the same time less than or
equal to 5. My current logic statement is set as:

=IF(1<=k1<=5, "1-5",false). This returns false.

I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results in an error.

Please help.

 

[Dave Patrick]

The serialized value of 0:05:00 = 0.00347222222222222

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Tried that but it returns it as false. This may have something to do with
| the 0:05
| formatting in the cell that I want to evaluate. That is, the 0:05 in k1 is
| returned based on an IF statement itself. Might this affect the result of
| getting false? Do you have other suggestions?
|
| Thanks.

 

[Bob Phillips]

Maybe you want

=IF(AND(TIME(1,0,0)<=K1,K1<=TIME(5,0,0)),"1-5")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Tried that but it returns it as false. This may have something to do with
the 0:05
formatting in the cell that I want to evaluate. That is, the 0:05 in k1 is
returned based on an IF statement itself. Might this affect the result of
getting false? Do you have other suggestions?

Thanks.

=IF(AND(1<=K1,K1<=5),"1-5")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I'm trying to use an IF statement for time based data. The data that I'm
evaluating is in the 0:05 time format, which is the same as [h]:mm

format
in

custom formatting under cell formats.

Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
if this value is greater than or equal to 1 and at the same time less

than
or

equal to 5. My current logic statement is set as:

=IF(1<=k1<=5, "1-5",false). This returns false.

I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results in

an
error.

Please help.

 

[Daren]

Tried this one and it's still returning a false. Thanks though. If you have
some other suggestions, please let me know.

Maybe you want

=IF(AND(TIME(1,0,0)<=K1,K1<=TIME(5,0,0)),"1-5")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Tried that but it returns it as false. This may have something to do with
the 0:05
formatting in the cell that I want to evaluate. That is, the 0:05 in k1 is
returned based on an IF statement itself. Might this affect the result of
getting false? Do you have other suggestions?

Thanks.

=IF(AND(1<=K1,K1<=5),"1-5")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I'm trying to use an IF statement for time based data. The data that I'm
evaluating is in the 0:05 time format, which is the same as [h]:mm format
in
custom formatting under cell formats.

Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
if this value is greater than or equal to 1 and at the same time less than
or
equal to 5. My current logic statement is set as:

=IF(1<=k1<=5, "1-5",false). This returns false.

I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results in an
error.

Please help.

 

[Bob Phillips]

What is in K1?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Tried this one and it's still returning a false. Thanks though. If you have
some other suggestions, please let me know.

Maybe you want

=IF(AND(TIME(1,0,0)<=K1,K1<=TIME(5,0,0)),"1-5")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Tried that but it returns it as false. This may have something to do with
the 0:05
formatting in the cell that I want to evaluate. That is, the 0:05 in k1 is
returned based on an IF statement itself. Might this affect the result of
getting false? Do you have other suggestions?

Thanks.

:

=IF(AND(1<=K1,K1<=5),"1-5")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I'm trying to use an IF statement for time based data. The data

that
I'm

evaluating is in the 0:05 time format, which is the same as [h]:mm format
in
custom formatting under cell formats.

Here's the scenario. The data in cell k1 is 0:05. I'm trying to evaluate
if this value is greater than or equal to 1 and at the same time

less
than

or
equal to 5. My current logic statement is set as:

=IF(1<=k1<=5, "1-5",false). This returns false.

I've also tried =IF(0:01<=k1<=0:05,"1-5",false), but that results

in
an

error.

Please help.

 

[Daren]

Thanks. Tried that as k1<=0.00347 but it still returned false

 

[Dave Patrick]

Didn't you mean?

=IF(AND(TIME(0,1,0)<=K1,K1<=TIME(0,5,0)),"1-5")

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| What is in K1?
|
| --
|
| HTH
|
| Bob Phillips
|
| (replace xxxx in the email address with gmail if mailing direct)

 

[Pete_UK]

I've posted a response in .functions which generates all your ranges.

Pete