IF Time Between Two Times in Excel: Shifts and Cutoffs

To check if a time is between two times in Excel, wrap AND inside IF: the time must be at or after the start and before the end. That covers office hours, shift windows and order cutoffs. This guide also shows the two cases that trip people up: windows that cross midnight, such as a 22:00 to 6:00 night shift, and cells that hold text or a full date and time instead of a plain time.

How Excel stores times#

Excel stores a time as a fraction of a day, which is why you can compare times with >= and < like any number. Midnight is 0, 6:00 is 0.25, 9:00 is 0.375 and 17:00 is about 0.708. One hour is 1/24 of a day.

You can write a time inside a formula in three ways, and they all give the same number:

Don't write B2>="9:00". In quotes, 9:00 is text, and Excel compares numbers and text very differently, so the answer is wrong. Dates work on the same idea, with whole numbers for days, as the date basics lesson shows.

The examples below use this clock-in list:

IF time between two times: the basic formula#

To test a normal window like 9:00 to 17:00, use AND so that both conditions must be true. In C2, type and fill down:

=IF(AND(B2>=TIME(9,0,0),B2<TIME(17,0,0)),"In hours","Out of hours")

• B2>=TIME(9,0,0) checks the time is 9:00 or later.
• B2<TIME(17,0,0) checks it is before 17:00.
• AND returns TRUE only when both are true, and IF turns that into a label.

Maya (9:20) and Sana (12:45) are In hours. Joel (8:55), Felix (17:05), Rosa and Ken are Out of hours.

Decide which end includes the boundary. Here 9:00 counts as in hours (>=) but 17:00 doesn't (<), so a time can never belong to two back-to-back windows. The same pattern works for numbers, as the guide to IF between two numbers shows. The IF function guide and AND function guide cover each function on its own.

Put the start and end times in cells#

Hard-coded times are fine for a quick check, but cells are easier to change. Type 9:00 in F1 and 17:00 in F2, then use:

=IF(AND(B2>=$F$1,B2<$F$2),"In hours","Out of hours")

The $ signs lock F1 and F2 so they stay put when you fill down. Change F2 to 18:00 and every row updates. To combine the time test with other conditions, such as weekday and time, see how IF and AND work together.

How to handle a time window that crosses midnight#

When the window runs past midnight, AND never works, because no time is both after 22:00 and before 6:00. Use OR instead: the time is either late in the evening or early in the morning.

=IF(OR(B2>=TIME(22,0,0),B2<TIME(6,0,0)),"Night","Day")

Rosa (22:30) and Ken (3:10) are Night. Everyone else is Day.

If the window is in cells and could be either kind, one formula handles both. With the start in F1 and the end in F2:

=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Inside","Outside")

MOD with 1 measures how far each time is past the start, wrapping around midnight. If that distance is less than the length of the window, the time is inside. With 22:00 and 6:00 in F1 and F2, Ken's 3:10 is 5 hours 10 minutes past the start, inside an 8-hour window, so he is Inside. With 9:00 and 17:00, the same formula gives the same results as the AND version. For night-shift lengths, see how to calculate time differences that pass midnight, and for a full rota, the shift schedule guide.

Times with dates: use MOD to compare only the time#

A timestamp such as 3 Nov 2026 14:35 is a date number plus a time fraction, so it is always far bigger than TIME(14,0,0). Strip the date first with MOD(A2,1), which keeps only the part after the decimal point.

A typical cutoff rule: orders placed before 14:00 ship the same day.

=IF(MOD(A2,1)<TIME(14,0,0),"Ships today","Ships tomorrow")

An order at 13:59 on any date ships today, and one at 14:00 ships tomorrow. To test the date part instead, such as "before 1 December", see IF with dates.

How to count the times between two times#

To count how many times fall in a window, you don't need a helper column of IF results. COUNTIFS accepts two conditions on the same column, one for each end:

=COUNTIFS(B2:B7,">="&TIME(9,0,0),B2:B7,"<"&TIME(17,0,0))

The result is 2 (Maya and Sana). The & joins each operator to the time, because a function can't sit inside the quotes. For a window that crosses midnight, add two counts instead: =COUNTIFS(B2:B7,">="&TIME(22,0,0))+COUNTIFS(B2:B7,"<"&TIME(6,0,0)) gives 2 (Rosa and Ken).

Why your IF time formula gives the wrong answer#

Most wrong results come from how the times are stored, not from the formula.

1. Times stored as text. Imported times are often text, which you can spot because they sit on the left of the cell. Excel treats text as larger than any number, so B2>=TIME(9,0,0) is always TRUE and B2<TIME(17,0,0) is always FALSE. Convert with VALUE(B2) or TIMEVALUE(B2), as in the exercise on this page.
2. The time is in quotes. B2<"17:00" compares against text. Use TIME(17,0,0) or 17/24.
3. AND for a midnight window. Switch to OR or the MOD version.
4. A hidden date. If the cell shows 14:35 but the formula bar shows a date as well, use MOD(B2,1).
5. Tiny rounding gaps. A time calculated by a formula can be a hair below the boundary, such as 16:59:59.999. If exact boundaries matter, compare whole minutes: ROUND(B2*1440,0)<17*60.

Practise it#

Do the exercise on this page: it uses text times, the most common real-world snag. Then build the logic step by step in the IF function lesson and the AND, OR and NOT lesson, where you type each formula and get instant feedback.