How to Calculate Hours Worked in Excel

Excel can turn a week of clock punches into payroll-ready hours with one subtraction per day, as long as you know how it stores time and format the results correctly. This guide builds a simple daily and weekly hours sheet, then covers overnight shifts, decimal hours, weekly totals, pay and rounding.

How Excel stores time

Excel stores every time as a fraction of a 24-hour day. Midnight is 0, 6:00 AM is 0.25, noon is 0.5 and 6:00 PM is 0.75. One hour is 1/24 of a day and one minute is 1/1440.

That has two practical consequences:

• Subtracting one time from another gives a fraction of a day. 4:30 PM minus 8:00 AM is 8.5/24, or about 0.3542, which Excel can display as 8:30.
• To get a number you can multiply by an hourly rate, multiply that fraction by 24: 8.5/24 × 24 = 8.5 hours.

Keep this in mind and nearly every hours formula makes sense.

Set up the sheet

Put one day per row, starting in row 2, with these columns:

Type times with a colon and a space before AM or PM (8:00 AM), or in 24-hour form (16:30). Excel recognizes both and, unless you’ve changed the alignment, right-aligns a real time. A left-aligned entry is usually text, which formulas can’t add reliably. To apply a format, select the cells, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and type the format code.

Calculate hours for a same-day shift

When a shift starts and ends on the same calendar day, subtract Time In from Time Out. In D2:

=C2-B2

For decimal hours in E2, multiply the same difference by 24:

=(C2-B2)*24

Copy both formulas down to row 6. Here is a sample week:

Notice how the minutes convert: 45 minutes is 0.75 hours, 20 minutes is 0.33 and 40 minutes is 0.67. Reading 8:45 as “8.45 hours” is the most common payroll mistake. The guide to convert time to decimal in Excel covers other conversion methods.

If E2 shows a time such as 12:00 PM instead of 8.50, Excel copied the time format from the source cells. Reformat column E as Number with two decimal places, or Custom 0.00.

These rows measure the span between two punches. If employees take an unpaid lunch, either subtract it (=MOD(C2-B2,1)*24-0.5 removes a 30-minute meal) or record Lunch Out and Lunch In punches as shown in the Excel timesheet with lunch breaks guide.

Handle shifts that cross midnight

If someone works 10:00 PM to 6:30 AM, =C2-B2 returns a negative number, because 6:30 AM is a smaller fraction of a day than 10:00 PM. Excel displays a negative time as #####. Use MOD to wrap the result into a positive part of a day:

=MOD(C2-B2,1)
=MOD(C2-B2,1)*24

The first returns 8:30 and the second returns 8.50. MOD gives the same answer as plain subtraction for same-day shifts, so you can use it in every row. An equivalent decimal formula is =(C2-B2+(C2<B2))*24, which adds one day whenever Time Out is earlier than Time In.

Both approaches assume a shift shorter than 24 hours. For longer shifts, record a date with each punch. The Excel night shift hours guide covers that case, plus night differential hours.

Total the week with [h]:mm

Add a total row. In D7 and E7:

=SUM(D2:D6)
=SUM(E2:E6)

E7 shows 42.75. D7 should show 42:45, but with the ordinary h:mm format it shows 18:45. The h code displays only the hour of the day, so anything past 24 hours wraps around (42:45 minus 24 hours is 18:45). To fix it, select D7, press Ctrl+1 (Cmd+1), choose Custom and enter [h]:mm. The square brackets tell Excel to show elapsed hours.

In Google Sheets, choose Format → Number → Duration, or Format → Number → Custom number format and enter [h]:mm.

Multiply hours by an hourly rate

Always multiply decimal hours, never the time value. Put the hourly rate in H1, say $18.00. The tempting formula =D7*H1 returns $32.06, because D7 actually holds 1.78125 (days), not 42.75. Use either of these instead:

=E7*$H$1
=D7*24*$H$1

Both give $769.50 at straight time (42.75 × $18.00). For a non-exempt employee, though, the 2.75 hours over 40 must be paid at time and a half under the Fair Labor Standards Act (FLSA):

=ROUND(MIN(E7,40)*$H$1+MAX(0,E7-40)*$H$1*1.5,2)

That returns $794.25: 40 × $18.00 = $720.00, plus 2.75 × $27.00 = $74.25. The guide to calculate overtime in Excel covers daily overtime and state rules, and the time card calculator does the same math without a spreadsheet.

Round punches and decimal hours

Many employers round each punch to the nearest 5, 6 or 15 minutes. A day has 96 quarter hours, so this rounds the time in B2 to the nearest 15 minutes:

=ROUND(B2*96,0)/96

Use 288 instead of 96 for 5-minute rounding and 240 for 6-minute (tenth-of-an-hour) rounding. With 15-minute rounding, 7:53 AM and 8:07 AM both become 8:00 AM, while 8:08 AM becomes 8:15 AM. =MROUND(B2,"0:15") does the same job but can leave tiny floating-point remainders, so the ROUND version is safer.

To keep decimal hours at exactly two places, wrap the hours formula:

=ROUND(MOD(C2-B2,1)*24,2)

Under 29 CFR 785.48(b), rounding to the nearest 5 minutes, tenth of an hour or quarter hour is acceptable only if it averages out so employees are fully paid for the time they actually work. A policy that always favors the employer is not. This is general information, not legal advice; state law may be stricter.

Make the sheet reusable

A few small steps turn a one-off calculation into a template you can copy every week:

• Convert the range to a table. Select A1:E6 and press Ctrl+T (Cmd+T). New rows pick up the formulas and formats automatically, and the total row can use the table’s own Total Row option.
• Validate the punches. Select B2:C6, open Data → Data Validation, choose Time, and allow values between 0:00 and 23:59. Typos such as 8.30 or 830 are then rejected instead of silently producing wrong hours.
• Lock the formulas. Unlock the input cells (Format Cells → Protection), then use Review → Protect Sheet so nobody overwrites columns D and E by accident.
• Keep one sheet per week and name each tab by its start date, such as 2026-10-05, so totals stay tied to a single FLSA workweek.

Common errors and fixes

• ##### in a cell. Either the column is too narrow or the result is a negative time. Widen the column first; if the hashes remain, the shift crosses midnight, so switch to =MOD(C2-B2,1).
• The weekly total looks too small. A total of 18:45 for a 42-hour week means the cell uses h:mm. Change it to [h]:mm.
• Decimal hours display as a clock time. Format the cell as Number (0.00) or General.
• Pay is about 1/24 of what it should be. The formula multiplied a time value by the rate. Multiply by 24 first.
• Wrong totals or #VALUE!. One of the punches is text, not a time. Test with =ISTEXT(B2). SUM skips text silently, and subtraction returns #VALUE! when Excel can’t read the text as a time. Convert a readable text time such as “8:30 AM” with =TIMEVALUE(B2) or =--B2, and retype anything else. A value typed as 8.30 is the number 8.3, not a time.
• Results like 8.4999999. Times imported from a time clock often include seconds or binary rounding noise. Round each punch to the minute with =ROUND(B2*1440,0)/1440, or round decimal hours to two places, before comparing them with a threshold such as 40.