Formula Calculate Hours in Excel

To calculate hours in Excel, subtract the start time from the end time and multiply by 24: =(end_time – start_time) * 24. This returns the elapsed time as a decimal number of hours (e.g., 2.5 for 2 hours 30 minutes). You can also use the TEXT function to format the result as hours and minutes, or the MOD function to handle times that cross midnight.

Formula

The core formula for calculating hours between two times in Excel is:

• Decimal hours: =(end_time - start_time) * 24
• Hours and minutes: =TEXT(end_time - start_time, "h:mm")
• Crossing midnight: =MOD(end_time - start_time, 1) * 24

In each formula, start_time and end_time are cell references containing Excel time values (e.g., 9:00 AM and 5:30 PM). Excel stores time as a fraction of a day, so 12:00 noon is 0.5, and 6:00 PM is 0.75. Subtracting start from end gives the elapsed fraction of a day; multiplying by 24 converts that to hours. For example, 0.104167 (2.5 hours) * 24 = 2.5 hours.

Worked Example

Using the default values from the calculator above (finish time = 50 minutes, distance = 10 km), let’s calculate the average pace and speed. First, convert 50 minutes to hours: 50 / 60 = 0.8333 hours. Then pace = 50 / 10 = 5 minutes per km, and speed = 10 / (50/60) = 12 km/h. The calculator shows pace = 5.00 min/km and speed = 12.00 km/h, matching these results.

Now, for an Excel time calculation: if you start work at 9:00 AM (cell A1) and finish at 5:30 PM (cell B1), the formula =(B1-A1)*24 returns 8.5, meaning 8 hours and 30 minutes. If you instead use =TEXT(B1-A1, "h:mm"), you get “8:30” as text, which is useful for display but not for further calculations.

How Excel Stores Time

Excel treats time as a decimal fraction of a 24-hour day. For instance:

• 12:00 AM (midnight) = 0.0
• 6:00 AM = 0.25
• 12:00 PM (noon) = 0.5
• 6:00 PM = 0.75
• 11:59 PM = 0.99931

This is why subtracting two times gives a fraction, and why multiplying by 24 is necessary to get hours. If you forget to multiply by 24, you’ll get a decimal like 0.354167 (which is 8.5 hours as a fraction of a day), which is not the number of hours you expect.

Handling Times That Cross Midnight

If a shift starts at 10:00 PM and ends at 6:00 AM the next day, a simple subtraction =end_time - start_time yields a negative value because Excel sees 6:00 AM as a smaller number than 10:00 PM. To fix this, use the MOD function: =MOD(end_time - start_time, 1) * 24. The MOD function wraps around the midnight boundary, returning the positive elapsed time. For example, =MOD(0.25 - 0.91667, 1) * 24 gives 8 hours.

Formatting the Result

When you multiply a time difference by 24, the result is a decimal number (e.g., 8.5). To display it as hours and minutes, you can:

• Keep the decimal and format the cell as a number with 2 decimal places (e.g., 8.50).
• Use a custom number format [h]:mm to show total hours beyond 24 (e.g., 32:15 for 32 hours 15 minutes).
• Use the TEXT function to convert to a text string like “8:30”.

Be aware that the [h]:mm format only works when the cell contains a time value (a fraction of a day), not a decimal hours number. So if you have 8.5 in a cell, formatting as [h]:mm will show 12:00 (because 8.5 days = 204 hours, which displays as 204:00 if you use the bracket format, but as 12:00 if you use a standard time format).

Common Mistakes

One frequent error is not multiplying by 24, leaving the result as a fraction of a day. Another is using the wrong format for the result cell—if you apply a time format to a decimal hours value, it will display incorrectly. Also, when subtracting times across midnight without MOD, you get a negative number, which Excel may display as a series of ##### symbols. To avoid this, either use the MOD formula or ensure the end time is on the next day (add 1 to the end time if start > end).

Alternatives and When to Use Them

For simple elapsed time within the same day, the basic subtraction works. For time sheets that span midnight, use MOD. If you need to sum hours across multiple days (e.g., weekly total), use the [h]:mm format on the sum cell so it shows total hours over 24. For time differences that include dates (e.g., start on Jan 1 at 10 PM and end on Jan 2 at 6 AM), subtract the full datetime values: =end_datetime - start_datetime and multiply by 24—this automatically handles the date change without MOD.

Frequently Asked Questions

How do I calculate hours between two times in Excel?

Subtract the earlier time from the later time and multiply by 24: =(end_time - start_time) * 24. This gives the number of hours as a decimal. For example, 9:00 AM to 5:30 PM yields 8.5.

Why is my time difference showing as #####?

This usually means the result is a negative number or the column is too narrow. If the end time is earlier than the start time (e.g., overnight shift), use =MOD(end_time - start_time, 1) to get a positive result. Also widen the column or change the number format.

How do I sum hours that exceed 24 hours?

Use the custom number format [h]:mm on the cell containing the SUM formula. This displays total hours beyond 24 (e.g., 30:15 for 30 hours 15 minutes) instead of rolling over to the next day.

Can I calculate hours worked including lunch breaks?

Yes. Subtract the break time from the total time: =(end_time - start_time - break_time) * 24. For example, 9:00 AM to 5:30 PM with a 30-minute break gives 8.0 hours.

What is the difference between decimal hours and hours:minutes format?

Decimal hours (e.g., 8.5) are a number you can use in further calculations, like multiplying by an hourly rate. Hours:minutes format (e.g., 8:30) is easier to read but stored as a fraction of a day, so you must multiply by 24 to get decimal hours.

Related

• Time Calculator From Speed and Distance
• Time Clock Converter Calculator: How It Works
• Time Clock Calculator With Seconds: How to Use

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2