How to find which shift a time falls in in Excel

Q: 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.

Q: How do I count clock-ins per shift?

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

Formula (Excel and Google Sheets)

=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)

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

Result on the example: one result per row in C2, filled down to C7
Works in: Excel 2021, 2024 and Microsoft 365
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

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.

=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)

Result"Morning"

B27:30 AM
The value in B2.
$E$2:$E$54 rows × 1 col: 12:00 AM, 6:00 AM, 2:00 PM, 10:00 PM
A range: 4 cells in one column.
$F$2:$F$54 rows × 1 col: "Night", "Morning", "Afternoon", "Night"
A range: 4 cells in one column.
-1-1
Makes the value negative.
XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)"Morning"
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueB27:30 AM
- lookup_array$E$2:$E$54 rows × 1 col: 12:00 AM, 6:00 AM, 2:00 PM, 10:00 PM
- return_array$F$2:$F$54 rows × 1 col: "Night", "Morning", "Afternoon", "Night"
- if_not_found(omitted)
- match_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1). 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
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)	Morning, Afternoon, Afternoon…	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.

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.

Find which shift a time falls in

In C2, return the shift for the clock-in time in B2 using the shift start times in E2:F5, then fill down to C7.

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").

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.