devLap

How to Calculate Hours Worked in Excel Without Breaking Payroll Math

javascript dev.to

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
Enter fullscreen mode Exit fullscreen mode

into:

8.00 decimal hours
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Result:

8.00
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Or if lunch is stored as 0:30:

=((B2-A2)-D2)*24
Enter fullscreen mode Exit fullscreen mode

For this example:

17:30 - 09:00 = 8:00
8:00 - 0:30 = 7:30
7:30 = 7.50 decimal hours
Enter fullscreen mode Exit fullscreen mode

So the payroll value is:

7.50
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Another example:

7 hours 45 minutes
= 7 + (45 ÷ 60)
= 7.75
Enter fullscreen mode Exit fullscreen mode

So:

7:45 → 7.75
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

If you use:

=(B2-A2)*24
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Or more simply:

=MOD(B2-A2, 1) * 24
Enter fullscreen mode Exit fullscreen mode

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

8.00 decimal hours
Enter fullscreen mode Exit fullscreen mode

If you also subtract a 30-minute unpaid lunch:

8.00 - 0.50 = 7.50
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Example:

Weekly total = 43.75
Regular      = 40.00
Overtime     = 3.75
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

At $20.00/hour:

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

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
Enter fullscreen mode Exit fullscreen mode

So 4.35 is 4 hours 21 minutes.

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

4 + (35 ÷ 60) = 4.58
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

For a normal shift:

=(B2-A2)*24 - (D2/60)
Enter fullscreen mode Exit fullscreen mode

A safer universal formula is:

=MOD(B2-A2,1)*24 - (D2/60)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

For clock times in Excel:

=(End - Start) * 24
Enter fullscreen mode Exit fullscreen mode

For overnight shifts:

=MOD(End - Start, 1) * 24
Enter fullscreen mode Exit fullscreen mode

For unpaid lunch:

=MOD(End - Start, 1) * 24 - (UnpaidLunchMinutes / 60)
Enter fullscreen mode Exit fullscreen mode

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.

Source: dev.to

arrow_back Back to Tutorials