Make a Simple Timesheet in Excel With Formulas

A timesheet in Excel records when someone started, stopped and took a break each day, then turns those times into hours. One formula does the daily hours, SUM adds up the week, and IF catches missing entries and overtime. This guide builds a one-week timesheet you can reuse every week, whether you track your own freelance hours or a small team's shifts.

How the timesheet is laid out#

The timesheet has one row per day and one column per piece of information. Here is the finished week:

Only the date, start, end and break are typed in. The day name, hours and total are all formulas. The dates are shown in day/month/year order; your Excel shows them in your own regional format.

Step 1: Enter dates and day names#

Type the Monday date in A2. In A3, type =A2+1 and fill it down to A6 with Ctrl+D (Mac: ⌘D). Each date is one day after the one above, because Excel stores dates as whole numbers counting up by one each day.

In B2, show the day name from the date:

=TEXT(A2,"ddd")

This returns Mon. Fill it down. Next week you only change A2, and every date and day name updates.

Step 2: Enter start and end times#

Type times with a colon, like 8:30 and 17:00. Use the 24-hour clock, or add a space and AM or PM, like 5:00 PM. Excel recognises these as times and right-aligns them.

Behind the scenes, Excel stores a time as a fraction of a day. Noon is 0.5 and 6:00 AM is 0.25. That's why the next step multiplies by 24. If a time sits on the left of the cell, Excel has stored it as text. Retype it with a colon and no extra spaces.

Step 3: Calculate daily hours#

Daily hours are the end time minus the start time, turned into hours, minus the break. In F2:

=(D2-C2)*24-E2/60

The result for Monday is 8.00. Here's how it works:

• D2-C2 is 17:00 − 8:30, which Excel stores as 0.354 of a day.
• *24 turns that into 8.5 hours.
• E2/60 turns the 30-minute break into 0.5 hours.

Fill the formula down to F6. Format column F as a number with two decimal places. Excel sometimes copies the time format from the inputs, and then 8.75 hours looks like a clock time. If someone works past midnight, use =MOD(D2-C2,1)*24-E2/60 so the result never goes negative. The guide to calculating hours worked and overtime covers night shifts and pay in more detail.

Step 4: Total the week with SUM#

SUM adds every number in a range. In F7:

=SUM(F2:F6)

The result is 41.25 hours. You can also click F7 and press Alt+= (Mac: ⌘⇧T) to let AutoSum write the formula for you. Check the range it picks before pressing Enter. The AutoSum shortcut guide explains how it chooses, and the SUM function guide covers adding several ranges at once.

A common mistake is adding the raw times instead of decimal hours. A clock-format total restarts after 24 hours, so 41:15 shows as 17:15. Using *24 in every row avoids this. If you prefer to keep times, format the total with the custom format [h]:mm; the square brackets let the hours go past 24.

Step 5: Use IF to catch missing times and overtime#

IF returns one value when a test is true and another when it's false. A timesheet uses it in three places.

Keep unfinished days blank#

On a day with no end time yet, the hours formula shows a negative number. Wrap it in IF:

=IF(D2="","",(D2-C2)*24-E2/60)

If D2 is empty, the cell stays blank. "" means empty text. SUM ignores text, so the weekly total still works.

Flag long days#

Add a Check column in G2:

=IF(F2>9,"Check","")

Thursday shows Check, because 9.5 hours is over the 9-hour limit used here. Change the 9 to your own rule.

Work out weekly overtime#

Overtime is the hours over a weekly limit. In F8:

=IF(F7>40,F7-40,0)

This week that's 1.25 hours. The 40-hour limit is only an example. Overtime rules depend on local law and contracts. The IF function guide and the article on how to use the IF function show more tests you can write.

Step 6: Turn it into a reusable template#

A few touches make the timesheet quick to reuse and hard to break:

• Shade the input cells (date, start, end, break) so it's clear what to type.
• Lock the formula cells. Unlock the input cells with Format Cells > Protection, then turn on Review > Protect Sheet. People can then type times without overwriting formulas.
• Add a name and sign-off row above the table for the employee, the week and an approver.
• Save as a template. Choose File > Save As and pick Excel Template (.xltx). Each new file starts with empty times and working formulas.

For a month, add rows for every working day and widen the SUM range. Hours feed straight into money: link the weekly total to a monthly budget in Excel or to a cash flow forecast if you bill by the hour.

Practise it#

Try the exercise on this page: weekly overtime with IF and SUM in one formula. Then practise SUM and AutoSum in a live spreadsheet, and practise the IF function, where you write tests that return one result or another, just like the timesheet checks.