Date & Time
First-In and Last-Out attendance times from badge logs (MINIFS & MAXIFS)
Find the earliest and latest recorded swipes for an employee and date, and calculate their elapsed span.
Summarize multi-swipe badge logs by employee and date
Security turnstiles and timeclocks record every door swipe—morning arrival, lunch exit, lunch return, and evening departure—as separate unsorted rows.
MINIFS(SwipeTime, EmpCol, Emp, DateCol, Date) finds the exact First-In clock fraction, MAXIFS(...) finds the exact Last-Out fraction, and (LastOut - FirstIn) * 24 converts the elapsed span into decimal hours.
| Employee (A) | WorkDate (B) | SwipeTime (C) | Role in Summary |
|---|---|---|---|
| Ava | 2026-05-04 | 08:30 (0.3542) | First In (MINIFS) |
| Ava | 2026-05-04 | 12:15 (0.5104) | Midday swipe (ignored) |
| Ben | 2026-05-04 | 07:45 (0.3229) | Different employee |
| Ava | 2026-05-04 | 17:15 (0.7188) | Last Out (MAXIFS -> 8.75 hrs) |
Total hours between First-In and Last-Out swipes
Pass DATE(2026, 5, 4) (or a date cell reference) rather than a text string so numeric date serials match cleanly across every locale, then multiply by 24 to express elapsed span in decimal hours.
=ROUND((MAXIFS(C2:C5, A2:A5, "Ava", B2:B5, DATE(2026,5,4)) - MINIFS(C2:C5, A2:A5, "Ava", B2:B5, DATE(2026,5,4))) * 24, 2)Check missing punches (single swipe = 0 span)
If an employee badged in once and forgot to badge out, COUNTIFS = 1 and MAXIFS equals MINIFS (0 hours)—flag single-swipe days for supervisor review!
=IF(COUNTIFS(A2:A5, "Ben", B2:B5, DATE(2026,5,4)) < 2, "Insufficient swipes", "At least two swipes")Inputs and interpretation
Earliest/latest swipes do not identify entry and exit directions. The elapsed span includes breaks and is not hours worked or hours physically on site. Two records do not prove completeness; duplicates and missing pairs need validation. The span formula requires at least two valid observations. For overnight shifts use full timestamps and a defined shift window instead of clock fractions grouped by calendar day.
Try the example
Use the original values or change them to test your understanding.