How to Calculate Hours Worked in Excel

Calculating hours worked in Excel is usually simple when shifts start and end on the same day. It gets easier to break when the sheet includes lunch breaks, overnight shifts, or weekly overtime checks.

Start with clean time fields before adding payroll logic.

The basic formula

If A2 contains the start time and B2 contains the end time, use:

Excel stores time as a fraction of a day. Multiplying by 24 converts the result into hours.

For example, if A2 is 9:00 AM and B2 is 5:30 PM, the result is 8.5.

Subtract unpaid breaks

If C2 contains an unpaid break in hours, subtract it from the total:

If breaks are recorded in minutes, convert them to hours:

That keeps the result as a decimal hour total, which is easier to sum across a week.

Handle overnight shifts

Overnight shifts need a small adjustment because the end time is technically on the next day. If B2 is earlier than A2, add one day before calculating hours:

This works for shifts like 10:00 PM to 6:00 AM. Without the adjustment, Excel may return a negative result or a confusing time value.

Roll daily hours into weekly hours

Once each day has a daily-hour value, sum the week:

In an employee schedule template, keep weekly hours visible next to the schedule grid. Managers should not have to hunt for the total when reviewing overtime or balancing shifts.

Flag overtime

For a basic overtime flag at 40 hours, use:

If the overtime threshold differs by employee type, state, policy, or role, put the threshold in its own column and reference that instead of hard-coding 40.

Common mistakes

The Griddy way

Hour formulas are easy to write once, but easy to damage when the schedule changes or new shift types get added.

"Add hours-worked formulas to this schedule, handle overnight shifts, subtract break minutes, and flag anyone over 40 hours"

Griddy can add the formulas, fix the formatting, and make the weekly review easier before the schedule is published.