Building the spreadsheet right

Time tracking in Excel: the sheet that actually adds up

Time tracking in Excel meets the legal obligation, because no form is prescribed. It rarely fails on the law and almost always at four points: the cell format, the night shift across midnight, the break deduction and the monthly total beyond 24 hours.

  • A ready-made template for 2026, free and without signing up
  • The four formulas that usually cause the trouble
  • Honest about what a spreadsheet cannot do

Last checked on4. September 2026

The cell format decides everything else

Excel treats times as fractions of a day: 12:00 is 0.5 internally. As long as the cells are formatted as times, that works. As soon as somebody types "8.5" instead of "8:30", the sheet carries on with eight and a half days, and the total is nonsense without any error message appearing.

The cells for clock-in, clock-out and break therefore get the format "hh:mm", and the total cell the format "[h]:mm". The square brackets are the point where most sheets fall over: without them Excel starts again at zero after 24 hours, and a monthly total of 168 hours appears as 0:00.

The four formulas that carry a time sheet

  • Working time for the day

    Formula
    =(C2-B2)-D2
    What to watch for
    B clock-in, C clock-out, D break. All three formatted as hh:mm.

  • A shift across midnight

    Formula
    =MOD(C2-B2,1)-D2
    What to watch for
    MOD catches the negative value when the end lies before the start.

  • Checking the break under the ArbZG

    Formula
    =IF((C2-B2)>TIME(9,0,0),TIME(0,45,0),IF((C2-B2)>TIME(6,0,0),TIME(0,30,0),0))
    What to watch for
    Sets the statutory minimum break instead of expecting it.

  • Monthly Total

    Formula
    =SUM(E2:E32)
    What to watch for
    Cell format [h]:mm, otherwise it resets at 24 hours.

The formulas are given in the English Excel spelling. In the German version they are REST, WENN, ZEIT and SUMME. The minimum breaks follow § 4 of the Working Hours Act: 30 minutes above 6 hours, 45 minutes above 9 hours.

What has to be in the sheet

For the record to meet the obligation, the total is not enough. What is required is the start, the end and the duration for each working day. For marginally employed staff and for the sectors listed in § 2a of the Act to Combat Undeclared Work, § 17 of the Minimum Wage Act applies as well: recorded no later than the seventh calendar day after the work, kept for at least two years.

  • One row per working day, not per week.
  • Start and end as clock times, not only the number of hours.
  • The break separately, because it is deducted.
  • Name, month and business in the header, so the sheet stands on its own.

The three limits no formula removes

They lie not in Excel but in the fact that a file is not a system. Knowing them lets you decide deliberately how long the sheet will do.

First, immutability: a cell can be changed retrospectively without anyone seeing it. In a dispute about overtime and in an inspection under § 17 MiLoG that is a problem of proof, and for tax-relevant records the GoBD expressly require a log of changes.

Second, the collecting: with ten people that is ten files a month somebody has to gather, check, add up and pass on. Third, the checking is missing: the sheet calculates, it does not warn. Whether eleven hours of rest were observed is there in the figures, but nobody notices it.

Frequent questions

What we are asked most often about Building the spreadsheet right.

Yes. The Working Hours Act prescribes no form, and handwritten records are permitted too. What matters is the content: the start, the end and the duration of the daily working time, complete and legible, kept for at least two years.

Because the total cell is formatted as "hh:mm". Excel then starts again at zero after 24 hours. The format "[h]:mm" with square brackets keeps counting beyond 24 hours. That is the most common fault in home-made time sheets.

With =MOD(C2-B2,1). The plain difference gives a negative value when the end lies before the start, and Excel then shows hash marks. MOD puts the value correctly into the range of one day, without having to carry a date.

On its own, no. For records subject to a retention duty the GoBD require changes to be logged and traceable. An Excel file does not do that, because every cell can be changed unnoticed. That matters above all where invoices arise from the hours.

Less a question of headcount than of three questions: does somebody have to collect files? Do invoices arise from the hours? Is there shift work with rest period limits? Anyone answering yes to all three pays for the software out of the time saved. With five people and a twenty-minute month end it is not worth it.

Question not answered here? Every step is explained in the help centre.

The template first, the system later.

The Excel template is free and stays that way. If you are further along: try it for 14 days, no credit card.

No credit card, cancel any time

Related

Where to go from here, and what waits for you there.