Dates and reporting
SUMIFS time totals: add shift durations past 24 hours without losing a day
Spreadsheets store time durations as fractions of a 24-hour day. Sum shifts by team with SUMIFS and multiply by 24 for decimal hours—avoiding the classic clock-rollover bug where 27 hours looks like 3:00.
Volunteer shift durations across two teams
A community clinic tracks five weekend shifts by team (column A), volunteer name (column B), and shift duration (column C). In spreadsheet time arithmetic, 1.0 equals 24 hours, so 6 hours is stored as 0.25, 7 hours 30 minutes as 0.3125, 9 hours as 0.375, and 12 hours as 0.5.
| Team (A) | Volunteer (B) | Clock duration | Stored day fraction (C) |
|---|---|---|---|
| Clinic | Maya | 9:00 | 0.375 |
| Outreach | Tomas | 6:00 | 0.25 |
| Clinic | Elena | 12:00 | 0.5 |
| Clinic | Nadia | 6:00 | 0.25 |
| Outreach | Sam | 7:30 | 0.3125 |
Convert the SUMIFS serial total to decimal hours with * 24
Put Clinic in E2. SUMIFS(C2:C6, A2:A6, E2) adds the three Clinic day fractions: 0.375 + 0.5 + 0.25 = 1.125 days. Multiplying that result by 24 converts days into plain decimal hours: 1.125 × 24 = 27 hours.
=SUMIFS(C2:C6,A2:A6,E2)*24Why 27 hours often masquerades as 3:00
The SUMIFS formula itself is not broken when a time sheet shows 3:00 instead of 27:00. A standard clock format such as h:mm displays only the time-of-day remainder after whole days are discarded: 1.125 days is 1 full day plus 0.125 days, and 0.125 × 24 is 3 hours.
Whenever you multiply a time total by an hourly rate or report grant hours, multiplying by 24 and using a standard Number format removes any ambiguity. In Microsoft Excel, if you prefer colon notation, apply the bracketed elapsed-time custom format [h]:mm to the unmultiplied serial 1.125 so Excel displays 27:00.
=SUMIFS(C2:C6,A2:A6,E2)Check Outreach hours and non-matching teams
Switch E2 to Outreach: 0.25 + 0.3125 = 0.5625 days, and 0.5625 × 24 = 13.5 hours (13 hours 30 minutes). Note that 13.5 in decimal hours means 13 hours and half an hour, not 13 hours 50 minutes. If E2 contains a team with no shifts such as Archive, SUMIFS returns 0.
Try extending a shift in the practice grid
Keep E2 as Clinic and change C5 (Nadia) from 0.25 (6 hours) to 0.375 (9 hours). The raw day fraction updates to 1.25 and the decimal-hour formula updates from 27 to 30.
Try the example
Use the original values or change them to test your understanding.