# Calculate hours worked from a timesheet

> Markdown version of https://stella.sh/how/money-household/calculate-hours-worked-from-timesheet for agents and LLMs.
> Total each person's weekly hours from a time clock export, unpaid breaks off, overnight shifts right and overtime over 40 shown, in Excel or with Stella (stella.sh).

For each shift, hours are clock-out minus clock-in, minus any unpaid break; in Excel `=MOD(out-in,1)*24-break/60` also handles shifts that run past midnight. Add them up per person per week, and anything over 40 is overtime under US federal rules. Stella (stella.sh) does it from the time clock export on your computer, for everyone at once, and points out a missing clock-out instead of guessing it.

- Checked: 14 November 2026
- Stella does it on: Windows, Mac, Linux

## Stella doing it

Recorded 14 November 2026 on Linux (Omarchy) with Claude Code with Opus, on a Claude plan.
Result: About 2 minutes. Checked afterwards against an answer key written before the run: weekly hours right for all six people with breaks off and overnight shifts counted across midnight, overtime over 40 right for each week, the missing clock-out pointed out and not guessed, and the export unchanged. The names and punches are fictitious, made for this run. The work is sped up 11×.

Video (46 s): https://stella.sh/media/proofs/money-household/calculate-hours-worked-from-timesheet/proof.mp4

Transcript:

- 0:00 Stella on a Linux computer. A two-week time clock export is in Documents.
- 0:02 The prompt: weekly hours for everyone, minus unpaid breaks, with overtime over 40.
- 0:14 Stella replies: it will flag missing punches and shifts past midnight instead of guessing.
- 0:20 Working through 60 shifts, sped up 11×.
- 0:32 Done: hours and overtime for six people, 483.4 hours in total, and two questions before payroll.

What Stella does:

1. Finds the time clock export in Documents: six people, 60 shifts over two weeks, 12-hour times and an unpaid-break column in minutes
2. Works out each shift with breaks taken off, including a night porter's shifts from about 10 pm to 6:45 am
3. Groups the shifts into Monday-to-Sunday weeks and splits each week into regular hours and overtime past 40
4. Leaves a shift with no clock-out at zero and asks what time she left, instead of guessing
5. Saves `Payroll_hours_2026-10-26_to_2026-11-08.xlsx` with Summary, Shifts and Issues tabs, the export left unchanged
6. Asks how you pay overnight shifts that cross into a new week, and shows what each choice would change

## The prompt to paste

```text
Add up the hours everyone worked from the time clock export in my Documents, for each of the two weeks (weeks start Monday), minus unpaid breaks, and show any overtime over 40 hours a week. Put it in a spreadsheet.
```

## Work it out in a spreadsheet

1. **Get times Excel can read.** Open the export in Excel or Numbers. Clock-in and clock-out should show as times, not text; if they're text like `9:02 AM`, use `=TIMEVALUE(B2)` in a new column.
2. **Hours for each shift.** In a new column: `=MOD(out-in,1)*24-break/60`, with the break in minutes. MOD makes an overnight shift (10 pm to 6:45 am) come out as 8.75 hours instead of a negative number.
3. **Give every shift a week.** For weeks starting Monday: `=date-WEEKDAY(date,3)` gives the Monday of that week. Decide whether an overnight shift belongs to the day it started; most time clocks do it that way.
4. **Total per person and week.** Insert a PivotTable with the name in Rows, week in Columns and Sum of hours in Values. Overtime for each cell is `=MAX(0,hours-40)`.
5. **Check the gaps.** Sort by clock-out and look for blanks: a missed punch makes a shift zero or wildly long. Ask the person and type the real time in; don't guess.

## Where Stella stops

- Overtime rules differ by state, country and contract: daily overtime, double time, or a different week. Stella applied the rule in the prompt (over 40 a week); tell it yours.
- It doesn't fill in missed punches. In the run it left the shift at zero until you add the real time.
- Not payroll or legal advice. Check the totals before anyone is paid.
- Names and times go to the AI model you use in Stella while it works. The spreadsheet is written on your computer.
- Recorded on Linux. On a Mac or Windows PC Stella may first install Python or a small spreadsheet library, which wasn't recorded.

## Questions

**How do I calculate hours worked with a lunch break?**
Clock-out minus clock-in, minus the unpaid break. In Excel with the break in minutes: `=MOD(out-in,1)*24-break/60`. A paid break stays in the hours.

**How do I handle a shift that goes past midnight?**
Use MOD as above so the hours come out positive. Then decide which week or day the hours belong to; Stella counted the whole shift on the day it started and showed what splitting at midnight would change.

**Can it do daily overtime, like California's over 8 hours?**
Yes, if you say so in the prompt. It applies the rule you give; it doesn't decide which law applies to you.

**Can it make invoices from the hours?**
Yes: see [generate invoices from a spreadsheet](https://stella.sh/how/documents/generate-invoices-from-spreadsheet). For car trips, see [mileage log for taxes](https://stella.sh/how/money-household/mileage-log-for-taxes).

**Is Stella free for this?**
Yes. Free with the ChatGPT or Claude plan you already have. Sign in with your ChatGPT plan, or use your Claude plan through Claude Code, and this task costs nothing extra. New accounts also get a small free trial of Stella’s own models.

**Can I control what Stella changes on my computer?**
Yes. Stella works on Windows, Mac and Linux with your own files and shows each step in the chat, so you can watch it and stop it at any point. Tell it what it may not do, as the prompts on these pages do (“don't delete anything without asking”). In the Downloads recording it deleted nothing until it was told to go ahead. It also refuses commands that would wipe a whole disk, your home folder or system folders.

Sources: [Microsoft Support: Calculate the difference between two times](https://support.microsoft.com/en-us/excel/calculate-the-difference-between-two-times-in-excel), [US Department of Labor: Overtime pay](https://www.dol.gov/agencies/whd/overtime).

## Related problems

- [Total your mileage log for taxes](https://stella.sh/how/money-household/mileage-log-for-taxes.md)
- [Generate invoices from a spreadsheet, one per customer](https://stella.sh/how/documents/generate-invoices-from-spreadsheet.md)
- [Summarize a spreadsheet by category and month, with totals](https://stella.sh/how/pdf-excel/summarize-spreadsheet-by-category.md)
- [Make a budget from your bank statements](https://stella.sh/how/money-household/make-a-budget-from-bank-statement.md)
- [Find duplicate charges on your credit card](https://stella.sh/how/money-household/find-duplicate-charges-on-credit-card.md)
- [Convert OFX, QFX and QIF files to Excel](https://stella.sh/how/money-household/convert-ofx-to-excel.md)

Get Stella: https://stella.sh/download. Free with the ChatGPT or Claude plan you already have.
