Date & Time
Assign attendance points based on late arrival time (with grace period)
Convert clock-in delay into integer minutes with ROUND(MAX(0, PunchIn - ShiftStart) * 1440, 0) and assign progressive attendance points via IFS.
Round time differences * 1440 before comparing grace thresholds
Comparing raw binary time fractions like PunchIn - ShiftStart <= TIME(0, 5, 0) often misclassifies an exact 5-minute arrival as 5.0000000001 minutes due to IEEE-754 floating-point representation.
Multiplying by 1,440 (minutes per 24-hour day) inside ROUND(MAX(0, B2 - A2) * 1440, 0) yields an exact integer minute count (0 for early/on-time arrivals), which IFS maps cleanly to policy tiers (<=5 min grace = 0 pts, 6–15 min = 0.5 pts, 16–60 min = 1 pt, >60 min = 2 pts).
| ShiftStart (A) | PunchIn (B) | Late Minutes | Attendance Points |
|---|---|---|---|
| 08:00 | 08:05 | 5 (within grace) | 0 |
| 08:00 | 08:12 | 12 | 0.5 |
| 08:00 | 08:35 | 35 | 1 |
| 08:00 | 09:15 | 75 | 2 |
Attendance point policy formula
MAX(0, B2 - A2) clamps early arrivals (e.g., 07:52) cleanly to 0 minutes late.
=LET(m, ROUND(MAX(0, B2 - A2) * 1440, 0), IFS(m<=5, 0, m<=15, 0.5, m<=60, 1, TRUE, 2))Rounded late minutes
Show the rounded minute count used by the illustrative points policy.
=ROUND(MAX(0, B2 - A2) * 1440, 0)Inputs and interpretation
Rounding to the nearest minute is a policy choice: 5 minutes 29 seconds becomes 5, while 5 minutes 30 seconds becomes 6. It does more than remove floating-point noise. For overnight shifts use full date-time values in a consistent time zone and define daylight-saving handling. The example points and grace period are not payroll or employment-law rules.
Try the example
Use the original values or change them to test your understanding.