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

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.

**Excel:** `=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Badge time | In night shift? |  | Shift starts | 0.9166666666666666 |
| Ruiz | 0.96875 |  |  | Shift ends | 0.25 |
| Hale | 0.1111111111111111 |  |  |  |  |
| Kwan | 0.25 |  |  |  |  |
| Moss | 0.9159722222222222 |  |  |  |  |
| Ortega | 0.24930555555555556 |  |  |  |  |
| Patel | 0.6041666666666666 |  |  |  |  |

Formula in C2, filled to C7: `=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")`

Result: Yes; Yes; No; No; Yes; No

## 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.

## Other ways

- `=IF(OR(B2>=$F$1,B2<$F$2),"Yes","No")`: 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")`: 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.

## 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,…)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-a-time-is-between-two-times