Freelance Timesheet in Excel: Hours Worked, Overtime and Invoice Total per Project

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: freelance-timesheet-overtime-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Ravina's 10 working days from 5 to 16 October 2026 (made-up values; ₹600 an hour, overtime 1.5×):

Of the 75 hours, 70.75 are regular and 4.25 are overtime on 3 days, worth ₹3,825. On 16 October she worked from 21:00 to 01:30. A plain End − Start gives a negative number, but MOD gives 4.5 hours.

2. Build it step by step

Step 1. Rate and multiplier

1. In B3 type the rate per hour (₹600) and in B4 the overtime multiplier (1.5).

Step 2. Daily rows

1. In A6:I6 type Date, Project, Start, End, Break (min), Hours, Regular, Overtime, Amount.
2. Type start and end as 24-hour times such as 09:30 and 18:00. Pick the project from a drop-down (drop-down lists).

Step 3. Hours worked

1. In F7 type =ROUND(MOD(D7-C7,1)*24-E7/60,2): (18:00 − 09:30) = 8.5 hours, minus 45 minutes = 7.75.
2. Times are fractions of a day, so ×24 turns them into hours. MOD(…,1) adds a day when the end is after midnight (time calculations, MOD in the sleep tracker).

Step 4. Regular and overtime

1. In G7 type =MIN(F7,8) and in H7 =MAX(0,F7-8). 9.5 hours gives 8 regular and 1.5 overtime.
2. In I7 type =G7*$B$3+H7*$B$3*$B$4: 8 × 600 + 1.5 × 900 = ₹6,150. Copy F7:I7 down.

Step 5. Totals and projects

1. In F17:I17 type =SUM(...) for each column: 75 hours, ₹46,275.
2. In L7 type =SUMIF($B$7:$B$16,K7,$F$7:$F$16) and in M7 =SUMIF($B$7:$B$16,K7,$I$7:$I$16) for hours and amount per project (SUMIF).
3. In M14 type =COUNTIF(H7:H16,">0"): 3 days with overtime.

Step 6. Colours

1. Select H7:H16 > Highlight Cells Rules > Greater Than 0, amber and bold.
2. Add a data bar on F7:F16 to see long days at a glance (data bars).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Forgetting ×24. D7 − C7 gives a fraction of a day (0.354), not 8.5 hours. Multiply by 24.
2. Plain subtraction for night work. 01:30 − 21:00 is negative. MOD(D7−C7,1) adds the missing day.
3. Typing 9.30 instead of 09:30. 9.30 is a number, not a time. Use a colon so Excel stores a time.
4. Subtracting break minutes as hours. A 45-minute break is 0.75 hours: divide by 60.
5. Paying overtime on the weekly total. This sheet counts overtime per day (above 8). If your agreement is per week, change the rule.

5. More real examples

Example 1: Hourly design or writing work. One row per task with the client's name in Project; SUMIF gives each client's monthly amount.

Example 2: Shift work at a shop. Night shifts from 22:00 to 06:00 work the same way with MOD.

Example 3: Fixed-price plus extra hours. Add a Fixed column per project and add it to the SUMIF amount.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate hours worked in Excel?

=(End-Start)*24 gives hours. Use =MOD(End-Start,1)*24 if work can end after midnight.

How do I subtract a break?

Type the break in minutes and subtract minutes/60 from the hours.

How do I split regular and overtime hours?

Regular = MIN(hours,8); overtime = MAX(0,hours-8).

How do I total hours per project?

Use =SUMIF(project_column, "Website", hours_column).

Why round the hours?

Times are stored as fractions, and tiny rounding errors can appear. ROUND(…,2) keeps hours clean, such as 7.75.

7. Quick quiz

Ten questions on this page. Sign in to save your score.