CODEWITHSUNDEEP
exceltimematch
user2703642
user2703642

Reputation: 193

Convert time to number and then match it to a another number

My problem is that I'd like to match time (hours) to a column with general numbers to get some values out from some other cells.

I managed to convert my date-time values to just the number of the hour using:

=TEXT(TIME(HOUR(B5),MINUTE(B5),SECOND(B5)),"h")

but when I try to match this to, for example, the number 10 it responds with #N/A.

How should I solve this?

Upvotes: 0

Views: 4161

Answers (2)

barry houdini
barry houdini

Reputation: 46371

Just use HOUR function, that returns a number directly, e.g.

=HOUR(B5)

You can use that in a comparison or MATCH function, e.g.

=IF(HOUR(B5)>10,"Late","Early")

Upvotes: 2

Jerry
Jerry

Reputation: 71578

You should be able to simply use:

=TEXT(B5,"h")

Now, the issue you're having is that TEXT returns a text value while you're probably looking up a number value. To convert the text value to a number value, you can multiply by 1 or add 0:

=TEXT(B5,"h")+0

=TEXT(B5,"h")*1

And that should do it!

Upvotes: 2

Related Questions