Calculate Hours Worked and Overtime in Excel

To calculate overtime in Excel, you first turn start and end times into hours worked, total the week, and then use IF (or MAX) to split the total into regular hours and overtime. A final ROUND keeps the pay to exact cents. This guide builds a one-week timesheet for one employee and a pay summary for a team. It is for supervisors, payroll and HR staff, and anyone checking their own hours.

How Excel stores time#

Excel stores a time as a fraction of a day. 12:00 noon is 0.5, 6:00 AM is 0.25, and one hour is 1/24. That is why subtracting two times gives a small decimal, not hours.

Multiply by 24 to turn a time difference into decimal hours: 0.364583 × 24 = 8.75. Decimal hours are easy to add, compare with 40 and multiply by a pay rate. Dates work the same way, with whole numbers for days, as explained in why Excel stores dates as numbers.

Step 1: Set up the timesheet#

Enter one row per day with the start time, end time and unpaid break in minutes. Type times with a colon, such as 8:30 or 17:15; Excel recognises them as times.

You can use 12-hour times too: type 5:15 PM with a space before PM. To stamp the current time into a cell, press Ctrl+Shift+; (Mac: ⌘;).

Step 2: Calculate hours worked each day#

Subtract the start from the end, multiply by 24, and take off the break. In E2:

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

The brackets make Excel subtract before it multiplies. The break is in minutes, so dividing by 60 turns it into hours. Monday is 8.75 hours on site minus 0.5 hours of break: 8.25. Fill down to E6.

If E2 shows a time such as 6:00 instead of 8.25, the cell picked up a time format from the cells it uses. Select column E and press Ctrl+Shift+~ (Mac: Control+Shift+~) for the General format, or choose Number with two decimals.

Shifts that end after midnight#

A shift from 22:00 to 6:00 gives a negative number with plain subtraction, because 6:00 is a smaller fraction than 22:00. MOD fixes it:

=MOD(C2-B2,1)*24

MOD with 1 keeps only the part of a day, so -0.6667 becomes 0.3333, which is 8 hours. The formula also works for normal day shifts, so you can use it everywhere.

Step 3: Total the week#

Add the daily hours with SUM. In E7:

=SUM(E2:E6)

The week comes to 43.75 hours. If you add time values instead of decimal hours, format the total as [h]:mm (Ctrl+1 or ⌘1 > Custom). The square brackets let the hours pass 24; without them, 43:45 would show as 19:45.

Step 4: Split regular hours and overtime with IF#

Regular hours are capped at the threshold, and overtime is anything above it. With the threshold of 40 in H1:

IF reads as: if the total is over 40, overtime is the total minus 40; otherwise it's 0. =MAX(0,E7-$H$1) gives the same result in a shorter formula. The threshold lives in its own cell, so a 38-hour or 44-hour rule needs one change. The IF function guide and the Excel IF function explained simply cover more IF patterns.

Some rules count overtime per day, for example over 8 hours in a day. Then use =MAX(0,E2-8) on each row and add those up instead.

Step 5: Calculate pay and round it#

Pay is regular hours at the normal rate plus overtime at a higher rate. With the hourly rate of 22.00 in H4 and the overtime multiplier of 1.5 in H5:

=ROUND(H2*H4+H3*H4*H5,2)

That is 40 × 22.00 = 880.00, plus 3.75 × 22.00 × 1.5 = 123.75, for 1,003.75.

ROUND with 2 decimals matters with rates like 18.75. Priya, in the exercise on this page, works 43.5 hours: 40 × 18.75 + 3.5 × 18.75 × 1.5 = 848.4375. Formatting would only show 848.44; ROUND stores exactly that, so payroll totals add up. How to round numbers in Excel explains ROUND, ROUNDUP and ROUNDDOWN.

You can also write the pay as one IF formula, which is the exercise:

=ROUND(IF(B2>40,40*C2+(B2-40)*C2*1.5,B2*C2),2)

Step 6: Summarise pay for a team#

For a team, put one row per employee with their weekly hours and rate, then fill the one-formula version down:

Mateo shows the boundary: exactly 40 hours is not over 40, so he gets no overtime. Check that your rule says "over 40" before you choose > rather than >=.

To bring in each person's rate automatically, look it up by employee ID from a staff list; the employee lookup sheet project builds exactly that. A weekly overtime total also makes a good line on a KPI summary sheet.

Rounding time to the nearest quarter hour#

Some workplaces round clock-in and clock-out times. On decimal hours, =ROUND(E2*4,0)/4 rounds to the nearest 0.25 hour (15 minutes): 8.2 becomes 8.25. Use ROUNDDOWN or ROUNDUP in place of ROUND if your policy always rounds one way.

Overtime rules come from local law and employment contracts, so the 40-hour threshold and 1.5× rate here are examples, not advice. Payroll work like this is part of the Excel skills for finance and accounting jobs.

Practise it#

Do the exercise on this page to work out overtime pay with IF and ROUND. Then practise the IF function, including IF that returns a calculation, and practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet.