How to Calculate Time in Excel: A Guide With Examples

You might be surprised to learn that Excel stores time as a fraction of a day. What that means is it doesn’t store 8:30 AM the way you see it, but rather, it stores it as 0.35417, which is 8.5/24. You could think of this as  35.4% of the full day. 

In this article, I will cover these and other important scenarios:

• Time difference: This covers calculating the gap between start and end time within the same day.
• Elapsed time: This measures the duration across multiple days using full timestamps.
• Time between dates: This covers calculating the gap between two calendar dates, either in days, hours, or minutes.
• Total time: This covers summing multiple time entries without hitting the 24-hour reset.
• Average time: This covers calculating the mean of multiple time entries.

How to Calculate Time Difference in Excel

Surely, at some point, you will need to calculate the gap between a start time and an end time (end time - start time). Whether you use the 12-hour clock or the 24-hour format, Excel sees them both as decimals. 

Excel doing that calculation and returning decimals isn’t the issue; making it readable is, and that’s where formatting comes in.

Take a look at this example:

Here, cell C2 will show 0.354167 (8.5/24) by default. That number is correct. It represents 8 hours and 30 minutes as a fraction of a day. To make it readable, apply the [h]:mm custom format.

Here is a step-by-step process for applying the correct format:

1. Select cell C2.

2. Press Ctrl + 1 to open the Format Cells dialog.

3. Click Custom.

4. In the Type field, enter [h]:mm or scroll down the options and click [h]:mm:ss and click OK.

Cell C2 now displays 8:30:00.

If you notice, [h]:mm was used instead of h:mm and the reason is that, without the brackets, Excel wraps the hour display back to zero after 24. A duration of 26 hours shows as 2:00 instead of 26:00. The brackets tell Excel to display the total hours, not the hour hand of a clock.

Excel supports 12-hour (AM/PM) as well as 24-hour formats. A cell showing 9:00 AM is the same as 09:00, and a cell showing 9:00 PM is the same as 21:00. The most important thing is to use the correct format in your cells so that calculations work as expected.

How to Calculate Elapsed Time in Excel

Elapsed time and time difference are two similar but distinct concepts. 

While time difference, as I mentioned earlier, calculates the gap between two time periods within the same day,  elapsed time spans across multiple days, and it requires full date and time values (timestamps) to calculate accurately.

The formula is this: 

= enddate_timestamp - startdate_timestamp

Both cells must contain a full date and time. For example, 3/1/2026 14:00 and 3/3/2026 9:30.

When you format cell C2 as [h]:mm, the result becomes 43:30, which just means 43 hours and 30 minutes of elapsed time. 

While you walk through these calculations, you might have some errors, such as your cell displaying as ######## or negative values, and I’ll explain later on how to resolve any errors like that. 

How to Calculate Time Between Dates in Excel

When it comes to the concept of calculating date differences, it’s slightly different from hours and minutes. Excel stores dates as serial numbers (January 1, 1900 is 1, January 2, 1900 is 2), so you can easily subtract dates and find the gap.

To calculate the number of days between dates, you simply subtract the start date from the end date (= end_date - start_date). 

Here is what I mean:

The result of this will be 9 days, but make sure you format the cell as a number; otherwise, Excel displays the number as a date instead of a count of days.

Once you get the number of days, you can easily convert that to calculate the number of hours and minutes, and you do that by multiplying:

• Hours between dates = (End_Date - Start_Date) * 24

• Minutes between dates = (End_Date - Start_Date) * 24 * 60

Take this example:

Hours between (number of hours between the two dates) = 216 hours

Minutes between dates (number of minutes between the two dates) = 12,960

In Excel, there is a function that allows you to calculate differences in specific units (days, months and years), and that function is called DATEDIF(). To carry out those calculations, here is the logic:

= DATEDIF(Start_Date, End_Date, "d")   → Total difference in days

= DATEDIF(Start_Date, End_Date, "m")   → Total difference in months

= DATEDIF(Start_Date, End_Date, "y")   → Total difference in years

If you want to understand the basic concepts in Excel, I recommend you check out our Excel Fundamentals track.

How to Calculate Total Time in Excel

Summing a list of time entries in Excel works just the same way you do the sum for other data types.

= SUM(C2:C15)

However, the issue comes when the sum of all the time entries crosses 24 hours, which then returns to 0. The fix for this is the same thing I’ve discussed from the start of this article: formatting the sum cell as [h]:mm. 

Without formatting the total cell (B7), the result would be 16:30, but when formatted, the result becomes 40:30.

To efficiently handle large time sums, the [h]:mm format handles any total size without an upper limit. Also, for reporting purposes, you may want to convert the total to decimal hours instead:

= SUM(B2:B6) * 24

This returns 40.0 rather than 40:00, which is a plain number that works cleanly in further calculations like multiplication, division, or in visuals.

How to Calculate Average Time in Excel

Excel calculates average time just like it does with other numbers using the AVERAGE() function; however, formatting issues cause problems in not seeing accurate results.

The logic to follow is this: 

= AVERAGE(B2:B8)

If the result cell is formatted as "General," you will see something like 0.354. Apply h:mm formatting, and it displays as 8:30. Use h:mm (without brackets) for averages, since they rarely exceed 24 hours.

The cell B6 might initially show 0.057292, but when you format it using h:mm, the result becomes 1:22:30 (1 hour, 22 minutes, and 30 seconds).

As covered in the elapsed time section, the same [h]:mm formatting applies in this situation as well.

There might also be situations where your datasets contain blank cells; Excel automatically ignores that when calculating averages, so if you have seven rows of data with two blank cells, Excel sees that as five.

However, if your cell contains 0, Excel counts that, and it could potentially affect your results, so what you do in this situation is to use the AVERAGEIF() function, or you can use that for conditional averages (when you want the average of rows that are greater than, less than, or equal to a certain value).

= AVERAGEIF(A2:A10, “>0”)

Common Errors When Calculating Time in Excel

There are some common errors you could potentially make when calculating time in Excel. Here are some of them and how to troubleshoot those errors when you run into them:

Negative time values or ####### 

######## means the column is too small to display the result; widen it and that resolves it. On the other hand, a negative value usually means the start and end cells were interchanged, or that dates are missing from an overnight calculation. 

You can fix this with:

= IF(B2 < A2, B2 + 1 - A2, B2 - A2)

Here’s how it works: if your start time is 6:00 PM and end time is 2:00 AM, B2 < A2 checks if the end time is less than the start time and if that is true, it adds one full day (1); otherwise, it subtracts normally.

Incorrect formatting 

If your total seems to be smaller than you expect or it doesn’t look accurate, you can resolve this by using the correct formatting ([h]:mm) as I mentioned earlier.

Text vs. time values 

If you enter a time as text (usually left-aligned in the cell, or typed with a leading apostrophe), any calculation you do fails. What you can do is to convert the text to a real-time value using: = TIMEVALUE(A2) then format the cell as h:mm.

24-hour rollover confusion 

When you carry out a SUM() that displays a small number, it’s more of a formatting issue than the formula. Apply [h]:mm to the total cell, and that fixes it.

Best Practices for Time Calculations in Excel

Some best practices I would recommend are:

• Make sure you check the cell format before assuming a formula is wrong. Most broken time results are display problems, not calculation errors.

• Use [h]:mm for any cell that calculates a sum or total. Use h:mm for individual time entries within a single day.

• Never enter time values as text. Type 8:30 and let Excel recognize it automatically. Avoid leading apostrophes or text labels mixed in with time data.

• Include dates in any calculation that spans midnight. A time-only value has no concept of the next day.

• Convert to decimal hours before using time in financial or rate formulas. Multiply the time value by 24 to get a plain number, then use that in multiplication or division.

• Keep time formats consistent across the entire worksheet. One column mixing AM/PM and 24-hour notation is a future debugging problem.

Conclusion

Calculating time in Excel is not complicated once you understand that it is just a fraction of a day. 

The scenarios we covered – time difference, elapsed time, multi-day spans, weekly totals, averages – reduce to the same core operations: subtraction, addition, and formatting. Formatting is the step most guides rush past, and it is the source of most confusion.

If you want to go further with Excel, our Excel Fundamentals track is a well-structured path that touches on functions, data types, and working with datasets in Excel.