# How to total hours per employee from a timesheet in Excel

Sum each person's out-minus-in with SUMPRODUCT and convert to hours: `=SUMPRODUCT(($A$2:$A$10=F2)*($D$2:$D$10-$C$2:$C$10))*24`.

**Excel:** `=SUMPRODUCT(($A$2:$A$10=F2)*($D$2:$D$10-$C$2:$C$10))*24` (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 | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Day | In | Out |  | Employee | Hours |
| Ana | Mon | 0.3333333333333333 | 0.6875 |  | Ana |  |
| Ben | Mon | 0.375 | 0.71875 |  | Ben |  |
| Cara | Mon | 0.3125 | 0.625 |  | Cara |  |
| Ana | Tue | 0.3333333333333333 | 0.7083333333333334 |  |  |  |
| Ben | Tue | 0.3854166666666667 | 0.7395833333333334 |  |  |  |
| Cara | Tue | 0.3125 | 0.6666666666666666 |  |  |  |
| Ana | Wed | 0.34375 | 0.6979166666666666 |  |  |  |
| Ben | Wed | 0.375 | 0.5416666666666666 |  |  |  |
| Cara | Wed | 0.3229166666666667 | 0.6354166666666666 |  |  |  |

Formula in G2, filled to G4: `=SUMPRODUCT(($A$2:$A$10=F2)*($D$2:$D$10-$C$2:$C$10))*24`

Result: 26.00; 20.75; 23.50

## How it works

`$D$2:$D$10-$C$2:$C$10` is each shift's length as a fraction of a day. Multiplying by the test for the employee keeps only that person's shifts, SUMPRODUCT adds them, and *24 turns days into hours: Ana worked 26 hours.

## Other ways

- `=SUM(FILTER($D$2:$D$10-$C$2:$C$10,$A$2:$A$10=F2))*24`: FILTER version for Excel 365 and Google Sheets.

## Common mistakes

- SUMIFS can't subtract two columns. Use SUMPRODUCT, as here, or add an hours column and SUMIFS that.
- Night shifts need MOD: `MOD($D$2:$D$10-$C$2:$C$10,1)`.

## Questions

**How do I total by employee and week?**

Add a date test, like `*(dates>=week_start)*(dates<week_start+7)`.

**How do I show the total as hours and minutes?**

Leave out *24 and use the custom format [h]:mm, so totals over 24 hours don't wrap.

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/total-hours-per-employee