Basic Timesheet Formula with Breaks

This calculator works out your net paid hours by subtracting unpaid break time from the total time between clock-in and clock-out. You enter the start time, finish time, and break duration in minutes, and it returns your net hours in decimal form (e.g., 7.50 hours) plus the equivalent in hours and minutes.

Formula

The basic timesheet formula with breaks is:

Net Hours = (Finish Time – Start Time) – Break Duration

Where:

• Finish Time – the clock-out time, usually in 24-hour format (e.g., 17:00 for 5:00 PM).
• Start Time – the clock-in time, also in 24-hour format (e.g., 09:00 for 9:00 AM).
• Break Duration – total unpaid break time in minutes (e.g., 30 minutes for lunch).

The result is expressed in decimal hours, which is the standard for payroll. For example, 7 hours and 30 minutes becomes 7.50 hours.

Worked Example

Using the default values from the calculator above:

1. Start time: 9:00 AM (09:00)
2. Finish time: 5:00 PM (17:00)
3. Break: 50 minutes

First, find the total elapsed time: 17:00 – 09:00 = 8 hours.

Convert the break to hours: 50 minutes ÷ 60 = 0.8333 hours.

Subtract the break from the total: 8 – 0.8333 = 7.1667 hours.

Rounded to two decimal places, the net hours are 7.17. That equals 7 hours and 10 minutes (since 0.1667 × 60 = 10 minutes).

The calculator gives the same result using the formula: (17 – 9) – (50 / 60) = 7.1667 → 7.17 hours.

Why Use Decimal Hours?

Most payroll systems and time-tracking software use decimal hours because they make multiplication by an hourly rate straightforward. For instance, if an employee earns $15 per hour and works 7.17 net hours, the gross pay is 7.17 × $15 = $107.55. If you used “7 hours and 10 minutes� directly, you would need to convert the minutes to a decimal anyway.

To convert minutes to decimal hours, divide the minutes by 60. Common conversions:

• 15 minutes = 0.25 hours
• 30 minutes = 0.50 hours
• 45 minutes = 0.75 hours
• 50 minutes = 0.83 hours

How to Handle Different Break Types

Not all breaks are unpaid. The formula above assumes the break is unpaid and should be deducted. If a break is paid (like a short rest break in some jurisdictions), you simply do not subtract it. For example, if an employee takes two 15-minute paid breaks, the net hours equal the total elapsed time.

If there are multiple unpaid breaks, add them together and subtract the sum. For instance, a 10-minute morning break and a 30-minute lunch break become a total of 40 minutes deducted.

Some companies also use a “rounding� rule (e.g., round to the nearest quarter-hour). That is a separate policy decision; the formula itself is exact.

Common Mistakes and How to Avoid Them

• Mixing 12-hour and 24-hour formats – Always use the same format. If you enter 5:00 PM as 5:00 in a 24-hour calculation, you’ll get a negative result. Convert PM times to 24-hour by adding 12 (except 12 PM itself).
• Forgetting to convert minutes to hours – If you subtract 30 directly from 8, you get 7.70, which is wrong. You must convert 30 minutes to 0.5 hours first.
• Not accounting for overnight shifts – If an employee starts at 10:00 PM and finishes at 6:00 AM the next day, the simple subtraction (6 – 22) gives a negative number. The correct approach is to add 24 hours to the finish time: (6 + 24) – 22 = 8 hours, then subtract breaks.
• Ignoring unpaid breaks entirely – This overstates hours worked and can lead to payroll errors.

How to Use This in a Spreadsheet

In Excel or Google Sheets, you can use the same formula with time values. For example, if A1 is the start time (09:00), B1 is the finish time (17:00), and C1 is the break duration in minutes (50), the formula would be:

=(B1-A1)*24 - C1/60

Multiplying by 24 converts the time difference (which Excel stores as a fraction of a day) into decimal hours. Then subtract the break in hours. The result is a decimal number like 7.17.

If you want the result in hours and minutes format, use:

=TEXT((B1-A1) - C1/1440,

Related

• COUNTIFS with Variable Table Column?
• Forza Horizon 6 Tokyo Tower Photo Challenge Location
• Tech Boom Vegas Bust: Nasdaq Gains and Las Vegas Housing Dips

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2