How to check if a time is between two times in Excel

Q: How do I include the end time?

Use <= instead: MOD(B2-$F$1,1)<=MOD($F$2-$F$1,1).

Q: How do I flag weekend night shifts too?

Combine it with a weekday test, such as AND(WEEKDAY(date,2)>5,…).

Formula (Excel and Google Sheets)

=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")

Measure from the start time with MOD: =IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No"). It works for windows that cross midnight.

Result on the example: one result per row in C2, filled down to C7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.

How it works

MOD(B2-$F$1,1) is how long after the shift start the time is, wrapping past midnight, and MOD($F$2-$F$1,1) is the length of the shift, 8 hours. A time is inside the shift when it is less than the shift length after the start. The same formula works for daytime windows like 9:00 to 17:00.

=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")

Result"Yes"

B211:15 PM
The value in B2.
$F$110:00 PM
The value in F1.
B2-$F$10.05208333333
The - operator subtracts.
MOD(B2-$F$1,1)0.05208333333
MOD: Remainder after division.
- numberB2-$F$10.05208333333
- divisor1
$F$26:00 AM
The value in F2.
$F$2-$F$1-0.6666666667
The - operator subtracts.
MOD($F$2-$F$1,1)0.3333333333
MOD: Remainder after division.
- number$F$2-$F$1-0.6666666667
- divisor1
MOD(B2-$F$1,1)<MOD($F$2-$F$1,1)TRUE
The < operator tests less than.
IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")"Yes"
IF: Returns one value if a test is true, another if false.
- logical_testMOD(B2-$F$1,1)<MOD($F$2-$F$1,1)TRUE
- value_if_true"Yes"
- value_if_false"No"not used

In Google Sheets

The same formula works in Google Sheets: =IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IF(OR(B2>=$F$1,B2<$F$2),"Yes","No")	Yes, Yes, No…Different result	Easier to read, but only right for windows that cross midnight. Works for overnight windows only.
=IF(AND(B2>=$F$1,B2<$F$2),"Yes","No")	No, No, No…Different result	Right for daytime windows; for an overnight one it never says Yes. Shows No for every row here.

Common mistakes

AND(B2>=F1,B2<F2) can't work for an overnight shift: no time is both after 10 PM and before 6 AM.
Times with dates attached need MOD(B2,1) first.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Check if a time is between two times

In C2, show "Yes" if the badge time is within the shift from F1 to F2 (the end time not included), and "No" otherwise. Fill down to C7.

Questions

How do I include the end time?

Use <= instead: MOD(B2-$F$1,1)<=MOD($F$2-$F$1,1).

How do I flag weekend night shifts too?

Combine it with a weekday test, such as AND(WEEKDAY(date,2)>5,…).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.