How to Calculate Hours in Excel

The simplest way to subtract two times

To calculate hours worked between a start time and end time, subtract the earlier time from the later time in two adjacent cells, then format the result as a number. If you clock in at 9:00 AM in cell A1 and clock out at 5:30 PM in cell B1, the formula =B1-A1 in cell C1 gives you the decimal result. Format that cell as a number (not time format) and you'll see 0.354166, which Excel stores as a fraction of a 24-hour day — in this case, 8.5 hours.

The reason you get a decimal instead of hours and minutes is that Excel treats time as a number between 0 and 1, where 1 equals 24 hours. When you subtract 9:00 AM from 5:30 PM, you get 0.354166 of a day, which is exactly 8.5 hours. To see it as 8:30 (eight hours and thirty minutes), format the cell as Time instead of Number. To see it as the decimal 8.5, multiply the result by 24: =(B1-A1)*24.

Key Takeaways

Subtract the start time from the end time using a straightforward formula like =B1-A1, then multiply by 24 if you want the result as a decimal number of hours.
Format cells as Time to see results as hours and minutes (like 8:30), or as Number to see decimal hours (like 8.5).
If your result shows a negative number or a very large number, check that your times are in the correct order and that both cells are formatted as time, not text.
For multiple time entries across a spreadsheet, copy the formula down the column so it calculates hours for each row automatically.
The HOUR, MINUTE, and SECOND functions let you extract individual parts of a time if you need to work with hours separately from minutes.

Setting up your time entries correctly

Before you calculate, make sure both your start and end times are actually stored as time values, not text. If you type 9:00 AM into a cell, Excel usually recognizes it as time. If you paste times from another source or type them in an unusual format, Excel might treat them as text, and your formula will return an error or a nonsense result.

To check: click a cell with a time in it and look at the formula bar at the top. If it shows 9:00 AM (or 9:00 in 24-hour format), it's time. If it shows '9:00 AM with an apostrophe at the start, it's text and you need to re-enter it. The fastest way is to click the cell, delete the content, and type the time again — Excel will recognize it as time on the second entry.

If you're entering times manually, use a consistent format. 9:00 AM and 17:30 (5:30 PM in 24-hour format) both work, but don't mix formats in the same column. Once your times are correct, the subtraction formula will work when ready.

Multiplying by 24 to get decimal hours

When you subtract two times in Excel, the result is a fraction of a day. To convert that fraction into hours as a decimal number, multiply by 24. The formula =(B1-A1)*24 takes the time difference and converts it to hours. If someone worked from 9:00 AM to 5:30 PM, this formula returns 8.5, which is easier to read and easier to add up if you're calculating total hours for a week.

This method works for any time span under 24 hours. If someone works a night shift that crosses midnight — clocking in at 10:00 PM and clocking out at 6:00 AM the next day — the formula still works, but you need to account for the date change. Put the start time and date in one cell (like 10:00 PM on 1/15) and the end time and date in another (like 6:00 AM on 1/16), then use the same formula. Excel handles the day boundary automatically.

Formatting cells to show hours and minutes

If you want the result to look like a clock time — 8:30 instead of 8.5 — format the cell as Time instead of Number. Right-click the cell with your formula result, select Format Cells, click the Number tab, and choose Time from the Category list. Then pick a format that shows hours and minutes, like 13:30:55 or 1:30:55 PM.

The time format is useful if you're reading the result by eye or if you need to display hours and minutes together. It's less useful if you're adding up hours across many rows, because Excel treats the formatted time as a fraction of a day, not as a number of hours. For calculations, stick with the decimal format (multiply by 24) or use the HOUR and MINUTE functions to extract the parts separately.

Copying the formula down for multiple rows

If you have a list of start and end times — like a timesheet with one row per day — enter your formula in the first row, then copy it down to all the other rows. Click the cell with your formula (for example, C1 with =(B1-A1)*24), then drag the small square at the bottom-right corner of the cell down to the last row with data. Excel automatically adjusts the cell references for each row, so C2 becomes =(B2-A2)*24, C3 becomes =(B3-A3)*24, and so on.

If dragging is awkward, use the keyboard instead. Click the cell with your formula, press Ctrl+C (or Cmd+C on Mac) to copy it, then select the range where you want to paste — for example, C2:C10 — and press Ctrl+V (or Cmd+V). The formula adjusts for each row automatically.

Handling times that cross midnight

If someone works a night shift that starts at 10:00 PM and ends at 6:00 AM the next morning, the straightforward subtraction formula breaks because the end time appears earlier in the day than the start time. The fix is to include the date with each time, so Excel knows the end time is actually the next day.

In cell A1, enter 1/15/2024 10:00 PM (the start date and time). In cell B1, enter 1/16/2024 6:00 AM (the end date and time the next day). Now the formula =(B1-A1)*24 correctly returns 8, because Excel sees that B1 is a full day plus 6 hours after A1. If you only have the time without the date, you can add 1 to the formula to account for the day change: =(B1-A1+1)*24 if B1 is earlier than A1. But including the actual dates is clearer and less error-prone.

Using HOUR and MINUTE functions for individual parts

If you need to extract just the hours or just the minutes from a time difference, use the HOUR and MINUTE functions. The formula =HOUR(B1-A1) returns only the hours part (for example, 8), and =MINUTE(B1-A1) returns only the minutes part (for example, 30). This is useful if you're building a report that needs to show hours and minutes in separate columns.

Keep in mind that HOUR and MINUTE work on the time portion of the result, not on the decimal. If the time difference is 8 hours and 30 minutes, HOUR returns 8 and MINUTE returns 30. If you need the total hours as a decimal (8.5), use the multiply-by-24 method instead. You can also combine them: =HOUR(B1-A1) + MINUTE(B1-A1)/60 gives you the total hours as a decimal with the minutes included as a fraction.

Frequently Asked Questions

Why does my formula show a huge number or a negative number?

A huge number usually means you forgot to multiply by 24, so Excel is showing the result as a fraction of a day (like 0.354166 instead of 8.5). A negative number means your end time is earlier than your start time — check that the cells are in the right order, or that both times are formatted as time and not text. If times cross midnight, make sure you included the date with each time.

Can I calculate hours worked across multiple days automatically?

Yes, if each day's start and end times are in separate rows. Use the formula =(B1-A1)*24 for each row, copy it down, then use SUM to add all the hours: =SUM(C1:C10). This gives you the total hours across all days in one cell. If times cross midnight, include the date with each time entry so Excel knows which day each time belongs to.

What if I only have the total time worked, not start and end times?

If the time is already in a cell as a duration (like 8:30), you can convert it to decimal hours by multiplying by 24: =A1*24. If it's stored as text (like the string "8:30"), you'll need to convert it to a time value first, which usually requires splitting the hours and minutes and rebuilding them as a formula.

How do I add up hours from multiple days into a weekly total?

Use the SUM function on the column with your daily hours. If your daily hours are in cells C1 through C5, the formula =SUM(C1:C5) adds them all together. Make sure each daily calculation uses the multiply-by-24 method so the results are in decimal hours, not time format — SUM works on both, but decimal is clearer for totals.

Can I round hours to the nearest quarter hour for payroll?

Yes, use the ROUND function. The formula =ROUND((B1-A1)*24*4,0)/4 rounds to the nearest quarter hour (0.25 hours). For rounding to the nearest half hour, use =ROUND((B1-A1)*24*2,0)/2. The number in the middle (4 for quarter, 2 for half) controls the rounding increment.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.