How to Calculate Hours Worked in Excel Without Breaking Payroll Math

RainyChen — Tue, 08 Sep 2026 12:41:33 +0000

If you have ever built a timesheet, you have probably run into the same problem twice: clock times are easy for humans to read, but payroll systems want durations as decimal hours.

A shift from 09:00 to 17:30 is not 9.5 on a timesheet. It is 8.00 hours if you subtract a 30-minute lunch, and payroll usually wants that written as 8.00, not 8:00.

In this article, we’ll walk through the Excel formulas, the edge cases, and the small time-math mistakes that cause real payroll problems.

1. Clock time and duration are not the same thing

Before touching Excel, separate two ideas:

Clock time answers “when did this happen?”

Examples: 09:00, 17:30, 22:00
Duration answers “how long did it last?”

Examples: 8 hours, 7.5 hours, 8.25 hours

A timesheet usually starts with clock times, but payroll needs durations.

That means you have to convert:

09:00 → 17:30

into:

8.00 decimal hours

Once the duration is a decimal number, payroll can multiply it by an hourly rate.

2. The core Excel formula

If Excel stores your start and end times correctly, the basic formula is:

=(End - Start) * 24

Why multiply by 24?

Because Excel represents time as a fraction of a day:

06:00 = 0.25 days
12:00 = 0.50 days
18:00 = 0.75 days

Multiplying by 24 converts that fraction into hours.

Example

A	B	C
Start	End	Hours
09:00	17:30	8.00

In C2:

=(B2-A2)*24

Result:

8.00

Make sure the result cell is formatted as a number, not as time.

3. Subtract an unpaid lunch break

If the shift has an unpaid lunch, subtract it before multiplying by 24.

Suppose:

Start: 09:00
End: 17:30
Unpaid lunch: 30 minutes

If lunch minutes are stored in D2:

=((B2-A2)*24) - (D2/60)

Or if lunch is stored as 0:30:

=((B2-A2)-D2)*24

For this example:

17:30 - 09:00 = 8:00
8:00 - 0:30 = 7:30
7:30 = 7.50 decimal hours

So the payroll value is:

7.50

Not 7.30.

4. Why 7.30 is wrong

This is the mistake that causes the most confusion.

If you worked 7 hours 30 minutes, the decimal version is not 7.30.

It is:

7 + (30 ÷ 60)
= 7 + 0.50
= 7.50 decimal hours

The decimal part represents a fraction of an hour, not the minute number.

Another example:

7 hours 45 minutes
= 7 + (45 ÷ 60)
= 7.75

So:

7:45 → 7.75

Not 7.45.

5. Handle shifts that cross midnight

A normal subtraction breaks when a shift goes overnight.

Example:

Start: 22:00
End:   06:00

If you use:

=(B2-A2)*24

Excel will return a negative number.

The fix is to add one day when the end time is earlier than the start time:

=((B2-A2) + IF(B2<A2, 1, 0)) * 24

Or more simply:

=MOD(B2-A2, 1) * 24

For 22:00 to 06:00, the result is:

8.00 decimal hours

If you also subtract a 30-minute unpaid lunch:

8.00 - 0.50 = 7.50

6. Convert HH:MM into decimal hours manually

If you have hours in one column and minutes in another:

Hours	Minutes	Decimal hours
7	45	7.75

Formula:

=A2 + (B2/60)

This is the safest formula when your data already separates hours and minutes.

Examples:

7h 15m = 7.25
7h 30m = 7.50
7h 45m = 7.75
8h 20m = 8.3333

Payroll systems commonly round to two decimal places, so 8.3333 may become 8.33.

7. Calculate a weekly total

Once every day is in decimal hours, the weekly total is just a sum:

=SUM(C2:C8)

Example:

Day	Decimal hours
Mon	8.00
Tue	7.50
Wed	8.25
Thu	7.75
Fri	8.00
Total	39.50

Do not sum clock times like 8:00, 7:30, and 8:15 and then treat the result as payroll hours. Convert first, then sum.

8. Split regular and overtime hours

If overtime starts after 40 hours in a workweek:

Regular hours:
=MIN(WeeklyTotal, 40)

Overtime hours:
=MAX(0, WeeklyTotal - 40)

Example:

Weekly total = 43.75
Regular      = 40.00
Overtime     = 3.75

If your hourly rate is in E2 and overtime is paid at 1.5×:

Regular pay:
=MIN(C2,40) * E2

Overtime pay:
=MAX(0,C2-40) * E2 * 1.5

Total pay:
=Regular pay + Overtime pay

At $20.00/hour:

Regular pay:  40 × $20.00 = $800.00
Overtime pay: 3.75 × $30.00 = $112.50
Total gross:  $912.50

9. Avoid the four most common mistakes

Mistake 1: Treating 4.35 as 4 hours 35 minutes

It is not.

4.35 hours = 4 hours + 0.35 of an hour
0.35 × 60 = 21 minutes

So 4.35 is 4 hours 21 minutes.

If you worked 4 hours 35 minutes, the payroll value is:

4 + (35 ÷ 60) = 4.58

Mistake 2: Subtracting paid breaks

Only subtract unpaid lunch or unpaid breaks if your employer does not count them as hours worked.

If a break is paid, leave it inside the duration.

Mistake 3: Mixing clock time and duration

A clock time like 14:30 is not the same as 14.30 hours.

14:30 is a time of day.

14.30 hours is a duration.

Convert durations to decimal hours before payroll calculations.

Mistake 4: Rounding before checking the rule

Some employers round clock times to the nearest:

5 minutes
6 minutes
10 minutes
15 minutes
1/10 hour
1/100 hour

The rounding rule matters because two different rules can produce different totals.

If you are auditing a paycheck, apply the same rule your employer uses before comparing your result.

10. A compact Excel template

A simple timesheet layout can look like this:

Day	Start	End	Unpaid lunch	Decimal hours
Mon	09:00	17:30	30	7.50
Tue	08:30	17:00	30	8.00
Wed	09:15	17:45	30	8.00
Thu	08:00	16:30	30	8.00
Fri	09:00	15:45	30	6.25

For a shift that may cross midnight:

=MOD(B2-A2,1)*24 - (D2/60)

For a normal shift:

=(B2-A2)*24 - (D2/60)

A safer universal formula is:

=MOD(B2-A2,1)*24 - (D2/60)

Then sum the decimal hours column.

Free calculator

If you want to check your Excel formulas without rebuilding the whole sheet, I built a free Weekly Timesheet Calculator that handles:

Clock-in and clock-out times
Unpaid lunch breaks
Overnight shifts
Weekly totals
Regular and overtime hour splitting
Estimated gross pay

It runs entirely in the browser and does not require an account:

Weekly Timesheet Calculator

There is also a full minutes-to-decimal chart here:

Minutes to Decimal Conversion Chart

Summary

The core idea is simple:

decimal hours = hours + (minutes ÷ 60)

For clock times in Excel:

=(End - Start) * 24

For overnight shifts:

=MOD(End - Start, 1) * 24

For unpaid lunch:

=MOD(End - Start, 1) * 24 - (UnpaidLunchMinutes / 60)

Convert first, sum second, and split overtime only after you know the weekly total.

That small order of operations will save you a lot of payroll headaches.

DEV Community: RainyChen

How to Calculate Hours Worked in Excel Without Breaking Payroll Math

1. Clock time and duration are not the same thing

2. The core Excel formula

Example

3. Subtract an unpaid lunch break

4. Why 7.30 is wrong

5. Handle shifts that cross midnight

6. Convert HH:MM into decimal hours manually

7. Calculate a weekly total

8. Split regular and overtime hours

9. Avoid the four most common mistakes

Mistake 1: Treating 4.35 as 4 hours 35 minutes

Mistake 2: Subtracting paid breaks

Mistake 3: Mixing clock time and duration

Mistake 4: Rounding before checking the rule

10. A compact Excel template

Free calculator

Summary