The fastest way to calculate hours worked

To calculate hours in Excel, subtract the start time from the end time, then format the result as a number or time. If you clock in at 9:00 AM and clock out at 5:30 PM, Excel can show you worked 8.5 hours in seconds. The math is straightforward — you just need to know which cells hold your times and how to tell Excel what format you want the answer in.

The most common setup is three columns: start time, end time, and hours worked. You'll put a formula in the hours column that does the subtraction, then format it so the answer makes sense. Excel treats time as a decimal (where 1 = 24 hours), so a raw subtraction gives you a number that looks wrong until you format it.

Key Takeaways

  • Subtract start time from end time using the formula =B2-A2, where A2 is the start time and B2 is the end time.
  • Format the result as a number by right-clicking the cell, choosing Format Cells, and selecting Number with two decimal places to see hours as 8.5 instead of 0.35417.
  • To get hours as a decimal automatically, multiply the subtraction by 24: =((B2-A2)*24) and format as a number.
  • If times cross midnight (you worked 11 PM to 7 AM), add 1 to the formula: =((B2-A2+1)*24) to account for the day change.
  • Use the SUM function to total hours across multiple days: =SUM(C2:C10) adds up all hours in that range.

Setting up your spreadsheet for time calculations

Start with three columns. Put your start times in column A, end times in column B, and leave column C blank for the hours calculation. Make sure both your start and end times are formatted as times — Excel needs to recognize them as time values, not text that looks like times.

To check if Excel sees your times correctly, click a cell with a time in it. Look at the formula bar at the top. If it shows something like 9:00:00 AM, you're good. If it shows the text "9:00 AM" with a small green triangle in the corner, Excel is treating it as text and your formula won't work. To fix this, delete the content and retype it, or use the Data menu to convert text to columns.

Once your times are set up, click on cell C2 (the first row where you want hours calculated) and type your formula. The simplest version is =B2-A2. Press Enter. You'll see a decimal number that doesn't look like hours yet — that's normal. The next step is formatting.

Formatting the result as hours and decimals

Right-click the cell with your formula result. Choose Format Cells from the menu. A dialog box opens. Click the Number tab if it's not already selected. In the Category list on the left, click Number. Set the decimal places to 2 (so you see 8.50 instead of 8.5 or 8.497). Click OK.

Now your result should show as a decimal number like 8.5 for 8 hours and 30 minutes. This is the format most payroll systems want. If you see a negative number like -15.5, it means your end time is in a different row or you reversed the formula — check that B2 (end time) comes after A2 (start time).

If you want to see the result as hours and minutes instead (like 8:30), right-click again, choose Format Cells, and in the Category list select Time. Pick a format that shows hours and minutes. This works, but decimal hours are usually easier to work with for payroll or totals.

Using the formula to multiply by 24

Some people prefer to write the formula as =((B2-A2)*24) instead of =B2-A2 followed by formatting. This does the same thing but makes the decimal hours visible in the formula itself. The *24 converts Excel's time decimal (where 1 = one full day) into hours.

Type this formula into cell C2: =((B2-A2)*24). Press Enter. Format the result as a number with two decimal places. You'll get the same answer as the simpler method, but some people find it clearer because the formula itself shows you're converting to hours.

Both methods are correct. Use whichever one makes sense to you. If you're sharing the spreadsheet with others, the simpler =B2-A2 method with time formatting is easier for someone else to understand at a glance.

Handling shifts that cross midnight

If someone works a night shift from 11:00 PM to 7:00 AM, the simple subtraction breaks. Excel sees 7:00 AM as earlier in the day than 11:00 PM, so the result is negative. To fix this, add 1 to your formula to account for the day change: =((B2-A2+1)*24).

The +1 tells Excel to add a full day to the calculation. So 7:00 AM the next day minus 11:00 PM the previous day becomes 8 hours, which is correct. If you're using the simpler =B2-A2 format, change it to =(B2-A2+1) and format as time.

A faster way to handle this: if you know which shifts cross midnight, put those times in a separate section and use the +1 formula only for those rows. For regular daytime shifts, use the standard formula without the +1.

Adding up total hours across multiple days

Once you have hours calculated for each day in column C, use the SUM function to total them. Click on a cell below your last entry (like C11 if your data ends at C10). Type =SUM(C2:C10). Press Enter. Excel adds up all the hours in that range.

If your data is scattered or you want to total only certain rows, you can list them individually: =SUM(C2,C4,C6) adds only those three cells. Or use SUMIF to total hours only if another column meets a condition — for example, =SUMIF(D2:D10,"Monday",C2:C10) would add up hours only for rows marked "Monday" in column D.

For a weekly total, make sure all seven days are included in your range. For a monthly total, include all the days in that month. The SUM function doesn't care how many cells you add — it just adds whatever numbers are in the range you give it.

Fixing common mistakes

If your formula shows an error like #VALUE!, Excel doesn't recognize one of your times as a time value. Check both cells — click on each one and look at the formula bar. If either shows text instead of a time, delete it and retype it, or convert it using Data > Text to Columns.

If your result shows as a time like 8:30:00 when you wanted a decimal, you formatted it as time instead of number. Right-click, choose Format Cells, select Number, and set decimal places to 2. If your result shows as 0.35417 instead of 8.5, you forgot to multiply by 24 or didn't format correctly — use the =((B2-A2)*24) formula and format as number.

If you see a negative number, your end time is earlier than your start time. Check that you didn't reverse the columns, and if the shift crosses midnight, add +1 to your formula. If the times look right but the formula still fails, make sure there are no extra spaces before or after the time — copy the cell, paste it into a new cell, and try again.

Frequently Asked Questions

Can I calculate hours if my times include seconds?

Yes. Excel treats seconds the same way as hours and minutes. If your start time is 9:00:15 AM and end time is 5:30:45 PM, the formula =((B2-A2)*24) still works and gives you 8.51 hours (accounting for the 30 seconds). Format as a number with two decimal places.

What if I want to see hours and minutes instead of a decimal?

Use the formula =B2-A2 and format the cell as time. Right-click, choose Format Cells, select Time, and pick a format that shows hours and minutes like [h]:mm. The brackets around h let you show more than 24 hours if someone worked overtime across multiple days.

How do I calculate hours for a whole week at once?

Enter the formula in the first row (C2), then copy it down to all other rows. Click C2, copy it, select the range C3:C8, and paste. Excel automatically adjusts the row numbers in each formula. Then use =SUM(C2:C8) in a cell below to total the week.

Can I subtract lunch breaks from the total?

Yes. If lunch is always one hour, use =((B2-A2)*24)-1 to subtract it automatically. If lunch varies, add a fourth column for lunch minutes, convert it to hours (divide by 60), and subtract that: =((B2-A2)*24)-(D2/60). This way you can enter different lunch lengths for different days.

What format should I use if I'm sending this to payroll?

Ask your payroll department, but decimal hours (8.5, 8.75) are most common. If they want hours and minutes, use the time format [h]:mm. If they want a specific format, they'll tell you — just make sure your formula is correct and let them choose how to display it.