Simple IF Statement with Time

[KuraiChikara]

Hello, I currently can't figure out a simple IF statement that involves time.

Example:
In cell A1 I have the time: 10:00
In cell A2 I have the formula: =IF(A1<="09:00","TRUE","FALSE")

Reading the formula out should say, "If cell A1 is less than or equal to 9:00 AM put True, otherwise put false.
However, when I put in that formula, it returns back TRUE which is wrong.

10:00 AM is not less than 9:00 AM, I don't know what I'm doing wrong.

 

[Joe4]

By writing it like:
A1<="09:00"
you are checking to see if A1 is less than the text entry of 09:00 (double-quotes denotes text).

Assuming that you value in A1 has no date component, use this instead:
A1<=(9/24)

This is because Excel tracks time as numeric value that is the fraction of one day.

 

[KuraiChikara]

That works but brings more questions to why my method didnt work:
I have another formula that works fine with the quotes.

Column A has ten percentage entries and B1 has a formula to count a particular percentage in the range.
Percent is numerical but the Greater-Than-Equal-To and Less-Than-Equal-To entries I have are in quotes. So why isn't that treated as text?

Formula: =COUNTIFS(A1:A10,">=90%",A1:A10,"<=99.99%")

90%	5
80%
50%
60%
23%
25%
99%
98%
96%
95%

 

[Joe4]

Notice that in your second formula, it is the entire condition (including the equality/inequality signs) that is in quotes, not just the number:
">=90%"

In the first one, it is only the number in quotes, so the number is treated like text.
<="09:00"

The location of the first quote makes all the difference in how it is treated.

Note that your original formula would work if you actually convert the text to time, like this:
=IF(A1<=TIMEVALUE("09:00"),"TRUE","FALSE")

 

[KuraiChikara]

I appreciate you showing me all of this and answering my questions and even showing me how I can adjust a text format into a time value but it still brings questions.
If the quote placements or no quote placements to represent an object as numeric or text is important, the following examples below, for me, conflict:

Formula: =IF(A1"<=09:00","TRUE","FALSE"): Quotes encompass the mathematical equation and doesn't work

Formula: =COUNTIFS(A1:A10,">=90%",A1:A10,"<=99.99%"): Quotes also encompass the mathematical equation and does work
I see no difference in order of operation or quotes placement but one works and one doesn't. The only difference is one's time and the other is a percent but both numeric, not text.

Additionally if no quotes represents numeric
Formula: =IF(A1<=09:00,"TRUE","FALSE"): No quotes at all doesn't work.
I don't get this one, if numerics are supposed to be represented with no quotes, the setup doesn't turn the 09:00 into a text but this is no good either.

Again I'm always learning excel. Every time I get an answer I usually have more questions.
Thank you so much

 

[Joe4]

It is important to understand the requirements of each of the arguments of the different functions you are using.

In the IF function, the first argument requires a complete mathematical expression that results in a True/False value to be returned.
That can be accomplished by writing an equality/inequality formula, or by using a function that returns a boolean function (such as IsOdd, IsError, etc).
So:
A1<="09:00"
A1<=(9/24)
A1<=TIMEVALUE("09:00")
are all valid boolean mathematical formulas (though they do not all do the same thing, as you discovered).

A1"<=09:00"
is NOT a valid formula. Remember, anything enclosed in quotes is treated as literal text. So in this instance, the "<=" is treated as text, not as an operator.

However, the COUNTIF function operates differently. The second argument does NOT want a complete formula that returns a boolean value.
All it wants is the condition of the formula (to be applied to the range in the first argument). It is not looking for the whole formula, only the condition, and it wants that condition returns as text.
So:
">=90%"
"<=99.99%"
are valid conditions, but not complete formulas.
Since you can build the conditions dynamically, the literal parts are in double-quotes.

For example, instead of seeing if the values in your designated range are >=90%, suppose that you wanted to see if they were greater than or equal than the number in cell D1. Then you could write the condition like this:
">=" & D1

Keep in mind that Excel has pretty good documentation on all their functions (the formula helper is useful), and there is lots of great information out there on the Web too, where you can find details explanations and examples of all these functions.

One final note on your original function:
=IF(A1<=(9/24),"TRUE","FALSE")
Since you have "TRUE" and "FALSE" in double-quotes, this will return the text string TRUE and FALSE. If you wanted to return the boolean values, you would drop the quotes around the TRUE/FALSE. And actually, if you wanted to do that, you don't even need the IF statement. You could write it as a straight-up boolen function like this:
=A1<=(9/24)
If that statement is True, it will return TRUE. If it is False, it will return FALSE.

Hope that helps.

 

[KuraiChikara]

I appreciate that break down extremely. I can't thank you enough.

 

[Joe4]

You are welcome.

The text/non-text thing often tricks people up at first (especially in writing VBA!).

The key fact to understand about date and times is that Excel actually stores them as numbers. So they are really just numbers with special date formats.
The number represents the number of days since 1/0/1900, and time is just the fraction of one day.
To see this in action, enter any date or time in Excel, and then change the format of that cell to "General". Then you will see it as Excel does.