How To Check If Time Is Between Two Times In Excel?

It is completely logical based on the formula you have created. You are telling Excel that if the time is not between the breakfast or lunch times, return dinner, so any time outside those periods will return dinner. You need to define the dinner period in the formula as another IF statement, and set "" as the value if false value.

Please Login or Register  to view this content.

but be careful you have circuit reference in c6.
And I know that 9:00 should be breakfast :-)

@elokaz, you believe the results are incorrect because C6 displays the time 10:47, and the result is "dinner".

You have made a couple of mistakes.

-----

First and foremost, you use NOW() in C6, which returns the date as well as time of day. So your formula in D6 would never have worked as intended, no matter how you might have tweaked it.

Ostensibly, you should use MOD(NOW(),1) in order to "extract" just time of day.

But really, you should use --TEXT(NOW(),"h:m"), which rounds to the second, then truncates to the minute, the same way that Excel displays time of day to the minute.

The reason is: NOW() returns time of day to the 1/100 second. So, for example, if the current date and time is 4/4/2020 03:59:59.51, that displays the time as 04:00. But MOD(NOW(),1)>=TIME(4,0,0) returns FALSE, which might surprise you.

-----

Second, 10:47 does not fit between any of the three time frames.

But in that case, your formula returns "dinner" because the time is not between 4:00-9:00 or 11:00-14:00.

The remedy is to also test between 19:00-23:00 explicitly, and return the null string(?) if the time is not between that time frame as well.

So your formula might be:

Please Login or Register  to view this content.

Note that there is no need to test C6<>"" separately, whether you use MEDIAN or AND with ">=" and "<=" operators.

Caveat: When I retested that formula in a "new" instance of the downloaded file, the formula returns zero(!). Obviously, that is "impossible". I believe the problem arises because of the self-circular reference in C6. If I replace the formula in C6, then undo, the formula above returns the null string, as expected.

The point is: The circular reference in C6 might cause problems with any valid solution.