Excel Timesheet Formulas: Hours Worked, Breaks, Overtime and Pay

Every formula you need for an Excel timesheet, from time out minus time in to weekly overtime and gross pay, with copy-paste examples.

Actualizado 2026-10-20

Esta guía está disponible en inglés por ahora; la traducción al español llegará pronto.

Excel stores a time as a fraction of a day: 6:00 AM is 0.25, noon is 0.5, 6:00 PM is 0.75. Every timesheet formula follows from that one fact. Subtract two times and you get a fraction of a day; multiply by 24 and you get hours.

The basic hours formula

With time in B2 and time out C2:

=(C2-B2)*24

9:00 AM to 5:30 PM gives (0.7292 − 0.375) × 24 = 8.5 hours. Format the result cell as a number with two decimals, not as a time, or Excel will show 12:00 PM instead of 8.5.

Deducting a break

If the break is typed in minutes in D2:

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

If the break is typed as a time span such as 0:30 in D2:

=(C2-B2-D2)*24

If you record four punches (in, lunch out, lunch in, out) in B2:E2:

=((E2-B2)-(D2-C2))*24

Shifts that cross midnight

10:00 PM to 6:00 AM gives a negative difference because 0.25 is smaller than 0.9167. Add a day when the out time is earlier than the in time:

=(C2-B2+(C2<B2))*24

(C2<B2) is TRUE or FALSE, which Excel treats as 1 or 0, so a full day is added only when needed. The same trick works inside the lunch formula.

Blank rows

A blank cell is zero, so an empty day would show −216 hours or similar nonsense. Guard it:

=IF(OR(B2="",C2=""),0,(C2-B2+(C2<B2))*24-D2/60)

Weekly total, regular and overtime

With hours in F2:F8:

Put the 40 in its own cell if your state or contract uses a different threshold. Overtime is owed per fixed 7-day work week, so on a bi-weekly or monthly sheet compute these three cells for each week separately rather than once for the period.

Daily overtime (California, Alaska, Nevada, Colorado)

Some states pay time and a half after 8 hours in a day (12 in Colorado) and California pays double time after 12. Add two columns per day:

Regular:   =MIN(F2,8)
Daily OT:  =MAX(0,MIN(F2,12)-8)
Double:    =MAX(0,F2-12)

Then test the weekly cap only against the regular column: =MAX(0,SUM(G2:G8)-40). Hours that were already daily overtime are not counted again.

Pay

With the hourly rate in B12:

Regular pay:  =F10*B12
Overtime pay: =F11*B12*1.5
Gross pay:    =F13+F14

Round money once, at the end: =ROUND(F15,2). Rounding each day’s pay and adding the rounded values can drift a cent or two from the true total.

Showing hours as H:MM

Payroll usually wants decimal hours, but people read H:MM more easily. To show 8.5 as 8:30, divide by 24 and format with the custom format [h]:mm. The square brackets stop the display wrapping at 24 hours, so a 45-hour week shows as 45:00 rather than 21:00.

Common mistakes

• Typing 900 instead of 9:00. Excel treats 900 as a number of days. Always use a colon or AM/PM.
• Formatting the hours cell as a time. 8.5 then displays as 12:00 PM.
• Using =C2-B2 without multiplying by 24, then wondering why the total is 0.35.
• Applying overtime to a two-week total. A 50-hour week plus a 30-hour week is 10 overtime hours, not zero.

If you would rather not maintain the formulas, the Time Card Calculator does all of this with overtime presets, rounding and export to Excel.

Bi-weekly layout

Keep each week in its own block with its own regular, overtime and gross pay cells, then add the two gross pay cells. Never test the two-week total against 80 hours: that quietly erases the overtime in a 50-hour week followed by a 30-hour week.

Frequently asked

How do I enter 24-hour time? Type 17:30 with a colon, then format the cell as hh:mm if you want it displayed that way. A bare 1730 is read as the number one thousand seven hundred thirty.

How do I stop a blank row showing a negative number? Wrap the formula in IF(OR(B2="",C2=""),0, ...) as shown above.

Can I round each punch to 15 minutes in Excel? =MROUND(B2,"0:15") rounds a time to the nearest quarter hour with the half point going up, which matches the 7-minute rule. Round the in and out cells, not the hours result.

How do I count the week’s overtime if the week starts on Wednesday? Your work week is whatever fixed 7-day period the employer uses. Lay out the rows Wednesday to Tuesday and the formulas are unchanged.

Why is my gross pay a cent off the payroll system? Rounding at a different step. Payroll systems typically keep unrounded hours and round once at the end; if your sheet rounds daily hours to two decimals first, the sum can differ by a cent.