DEV Community

Cover image for How to Calculate Hours Worked in Excel Without Breaking Payroll Math
RainyChen
RainyChen

Posted on

How to Calculate Hours Worked in Excel Without Breaking Payroll Math

#excel #javascript #productivity #tutorial

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.

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.