IF statement for time based data

[Guest]

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.

 

[Guest]

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

 

[Guest]

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

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.

 

[David Biddulph]

If it doesn't work for you, then I guess that your data in K1 is not
actually a time, but text. If it's really a time, then if you change the
format temporarily to Number it'll show was 0.00347222222222222
--
David Biddulph

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

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.

 

[Guest]

Daren...I also like to learn from ur question...I hope we have a solution..

 

[Guest]

My suggested formula returns a blank if the time in K1 is not equal to or
between the specified limits.

How is K1 generated, is there a formula in that cell? The actual value may
be slightly above 0:05, e.g. if K1 contains 0:05:01 but the cell is formatted
as h:mm. In this situation the value is above five minutes so the result is
correct.

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

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.

 

[Guest]

K1 is generated by an IF statement. The value in the cell is exactly 0:05.
What I'm trying to do is assign a range to this value and others. As an
example, 0:05 would be caputred in the range 1-5, which I had previously as
"1-5". Also, 0:08 would be captured in the range 6-10, which would be
"6-10". The goal is to assign incremental ranges to the various actual
times. The cell format for the range is [h]:mm, as is the 0:05 cell.

Thanks and look forward to hearing back from you.

My suggested formula returns a blank if the time in K1 is not equal to or
between the specified limits.

How is K1 generated, is there a formula in that cell? The actual value may
be slightly above 0:05, e.g. if K1 contains 0:05:01 but the cell is formatted
as h:mm. In this situation the value is above five minutes so the result is
correct.

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

:

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.

 

[Guest]

Yeah, your's are good also. Kinda tough work some time.

Daren...I also like to learn from ur question...I hope we have a solution..

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.

 

[Guest]

How many ranges do you have? If there are more than a few then you might be
better off with some sort of LOOKUP formula rather than multiple IFs.

If K1 was exactly 0:05 then the formula I suggested should return "1-5",
what's the formula in K1?

K1 is generated by an IF statement. The value in the cell is exactly 0:05.
What I'm trying to do is assign a range to this value and others. As an
example, 0:05 would be caputred in the range 1-5, which I had previously as
"1-5". Also, 0:08 would be captured in the range 6-10, which would be
"6-10". The goal is to assign incremental ranges to the various actual
times. The cell format for the range is [h]:mm, as is the 0:05 cell.

Thanks and look forward to hearing back from you.

My suggested formula returns a blank if the time in K1 is not equal to or
between the specified limits.

How is K1 generated, is there a formula in that cell? The actual value may
be slightly above 0:05, e.g. if K1 contains 0:05:01 but the cell is formatted
as h:mm. In this situation the value is above five minutes so the result is
correct.

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

:

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

:

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.

 

[Guest]

Tried that also but it doesn't work when I assign it as the upper control
boundary (i.e. k1<=0.00347. It's still returning false

If it doesn't work for you, then I guess that your data in K1 is not
actually a time, but text. If it's really a time, then if you change the
format temporarily to Number it'll show was 0.00347222222222222
--
David Biddulph

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")
:

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.

 

[Guest]

Ok, your formula worked when the exact value in the cell was 0:05. Thanks!!!!

My suggested formula returns a blank if the time in K1 is not equal to or
between the specified limits.

How is K1 generated, is there a formula in that cell? The actual value may
be slightly above 0:05, e.g. if K1 contains 0:05:01 but the cell is formatted
as h:mm. In this situation the value is above five minutes so the result is
correct.

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

:

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.

 

[Pete_UK]

This formula will generate all your ranges, 1-5, 6-10, 11-15 etc by
calculation rather than multiple IFs - the only IF checks for K1 being
at least 0:01

=IF(K1*60*24>=1,TEXT(INT((K1*60*24+4)/5)*5-4,"0")&"-"&TEXT(INT((K1*60*24+4)/5)*5,"0"),"0")

Hope this helps.

Pete

K1 is generated by an IF statement. The value in the cell is exactly 0:05.
What I'm trying to do is assign a range to this value and others. As an
example, 0:05 would be caputred in the range 1-5, which I had previously as
"1-5". Also, 0:08 would be captured in the range 6-10, which would be
"6-10". The goal is to assign incremental ranges to the various actual
times. The cell format for the range is [h]:mm, as is the 0:05 cell.

Thanks and look forward to hearing back from you.

My suggested formula returns a blank if the time in K1 is not equal to or
between the specified limits.

How is K1 generated, is there a formula in that cell? The actual value may
be slightly above 0:05, e.g. if K1 contains 0:05:01 but the cell is formatted
as h:mm. In this situation the value is above five minutes so the result is
correct.

Tried it, but it returns a blank. I also tried just 1 & 5 for the times
(e.g., k1>=1), but that didn't help either.

:

Try this

=IF(AND(K1>="0:01"+0,K1<="0:05"+0),"1-5","")

:

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]

multi-posted

--

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.

 

[Guest]

Try this:

=IF(AND(K1>=--"0:01",K1<=--"0:05"),"1-5","")