# How to find which shift a time falls in in Excel

List each shift's start time, starting at midnight, and use an approximate match: `=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)`.

**Excel:** `=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Clock-in | Shift |  | Starts at | Shift |
| Ana | 0.3125 |  |  | 0 | Night |
| Ben | 0.90625 |  |  | 0.25 | Morning |
| Cara | 0.5833333333333334 |  |  | 0.5833333333333334 | Afternoon |
| Dev | 0.09375 |  |  | 0.9166666666666666 | Night |
| Eli | 0.9791666666666666 |  |  |  |  |
| Fay | 0.24583333333333335 |  |  |  |  |

Formula in C2, filled to C7: `=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)`

Result: Morning; Afternoon; Afternoon; Night; Night; Night

## How it works

Times are fractions of a day, so 6:00 AM is 0.25 and they compare like numbers. Match mode `-1` picks the latest start at or before the clock-in: 7:30 AM falls in Morning. The night shift runs past midnight, so it is listed twice, at 12:00 AM and at 10:00 PM, and times on both sides of midnight find it.

## Other ways

- `=VLOOKUP(B2,$E$2:$F$5,2,TRUE)`: The approximate VLOOKUP works too, with the start times sorted from earliest to latest.

## Common mistakes

- Without the 12:00 AM row, a 2:15 AM clock-in returns #N/A, because no start time is earlier.
- If column B holds full timestamps (date and time), drop the date first: `XLOOKUP(MOD(B2,1),…)`.
- Times typed as text, like "7:30", don't compare as times. Check with ISNUMBER.

## Questions

**How do I flag clock-ins that are late?**

Compare with the shift's start: `=B2-XLOOKUP(B2,$E$2:$E$5,$E$2:$E$5,,-1)>TIME(0,10,0)` is TRUE when more than 10 minutes after the start.

**How do I count clock-ins per shift?**

Fill the shift column first, then use `=COUNTIF(C2:C7,"Night")`.

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/shift-for-a-time