# How to Calculate Time in Excel (A Complete Tutorial and Helpful Formulas)

If you’ve been relying on Excel to manage your projects and need help with time calculations, you’re not alone. Figuring out the elapsed time of a project or task is crucial for team leads that need to know what work should be prioritized and how to properly allocate resources.

Simply put, time values are tricky in Excel—especially when calculating time difference, hours worked, or the difference between two employees.

Luckily for you, we’ve done all the hard work to help format cells and calculate elapsed time in Excel faster. Let’s get confident about using Excel for project time tracking and discover the various ways of figuring time.

How to Calculate Time in Excel (A Complete Tutorial and Helpful Formulas)

## Types of Time Calculations in Excel

There isn’t a single formula or format for calculating time in Excel. It depends on your dataset and the goal you want to achieve. Most use a custom format (number over text function) to understand time and date values, or the time difference value.

But there are two types of calculations to get you started, which include:

• Add time: If you need to obtain the sum of two time values to get a total
• Add up to less than 24 hours: When you need to know how many hours and minutes it’ll take to complete two simple tasks
• Add up to more than 24 hours: When you need to add the estimated or actual elapsed time of multiple slow, complex tasks
• Subtract time: If you need to get the total time between a start and end times
• Subtract times whose difference is less than 24 hours: When you need to figure out the hours elapsed since a team member started to execute a simple task
• Subtract times whose difference is more than 24 hours: When you need to know the hours that have gone by since your project started

## How to Calculate Time in Excel

Let’s go through a few formulas for time calculations in Excel so you get down to the exact hours, minutes, and seconds in your custom time format.

### Time Difference in Excel

Before we teach you how to calculate time in Excel, you must understand what time values are in the first place. Time values are the decimal number to which Excel applied a time format to make them look like times (i.e., the hours, minutes, and seconds).

Because Excel times are numbers, you can add and subtract them. And the difference between a start time and an end time is called “time difference” or “elapsed time.”

These are the steps to subtract times whose difference is less than 24 hours:

1. Enter the start date and time in cell A2 and hit Enter. Don’t forget to write “AM” or “PM”
2. Enter the end time in cell B2 and hit Enter
3. Enter the formula =B2-A2 in cell C2 and hit Enter. An example of subtracting time on the same day in Excel

WARNING
As you can tell from the figure, this formula doesn’t work for times that belong to different days. This example shows the time difference between two time values within a 24-hour time period.

4. Right-click on C2 and select Format Cells. The custom value can be set up in various date format options

5. Choose the Custom category and type “h:mm”

SIDE NOTE
You might wish to obtain the time difference expressed in hours or hours, minutes, and seconds. If so, type “h” or “h:mm:ss,” respectively. This will help you avoid negative time values.

6. Click OK to see the total change to a format describing only the number of hours and minutes in the elapsed time between A2 and B2.

Check out our detailed blog to find a list of time management strategies you can use right away!

### Date and time in Excel

In the previous section, you learned how to calculate hours in Excel. But the formula you learned only applied to time differences of less than 24 hours.

If you need to subtract times whose difference is more than 24 hours, you need to work with dates besides times. Do it like this (format cells dialog box):

1. Enter the start time in cell A2 and hit Enter.
2. Enter the end time in cell B2 and hit Enter.
3. Right-click on A2 and select Format Cells.
4. Choose the Custom category and type “m/d/yyyy h:mm AM/PM.”
5. Click OK to see A2 change to a format starting with “1/0/1900” and adjust the date.
6. Use the Format Painter to copy the formatting of A2 to B2 and adjust the date.
7. Enter the formula =(B2-A2)*24 in cell C2 and hit Enter to see the total change to a format describing the number of hours in the time elapsed between A2 and B2.

WARNING
You must apply the “Number” format to C2 to get the correct value in the above formula.

### Sum time in Excel

Suppose you want to sum up the time difference between your team members working on multiple project tasks. If those durations add up to less than 24 hours, follow these steps:

1. Enter one duration in cell B2 (with the format h:mm) and hit Enter.
2. Enter the other duration in cell B3 and hit Enter.
3. Enter the formula =B2+B3 in cell B4 and hit Enter. Example of combining times that equal less than 24 hours in Excel formulas (Column B)

PRO TIP
If you prefer clicking a single button instead of entering a formula, you may position the cursor in B4 and hit the Σ (or AutoSum) button in the Home tab. The button will apply the formula =SUM(B2:B3) to the cell in the above example.

1. Enter one duration in cell B2 (with the format h:mm) and hit Enter. (Warning: Beware that the maximum duration is 23 hours and 59 minutes)
2. Enter the other duration in cell B3 and hit Enter.
3. Enter the formula =B2+B3 in cell B4 and hit Enter.
4. Right-click on B4 and select Format Cells.
5. Choose the Custom category and type “[h]:mm;@.”
6. Click OK to see B4 correctly displaying the sum of the times in B2 and B3. Example of combining values that equals more than 24 hours (Column B)

If you’d like to know more about Excel project management, our blog is the perfect place to start!

More Excel Resources:

## The Complete List of Formulas for Calculating Time in Excel

It pays to know the formulas when you’re calculating time difference in Excel. To get your time difference and value set up correctly, we’ve provided a list of formulas to make your calculations easier.

## An Easier Alternative to Calculating Time in Excel

Although the formulas in this article work, there’s a better way than Excel to calculate time—ClickUp!

Sure, spreadsheets are great for managing data, but what about your day-to-day tasks? And how do you communicate with your team to show these times in a simple and clear way? Arrange all of your tasks on a grid alongside their key details including status, due date, assignees, and more with Table view in ClickUp

ClickUp’s Table view keeps track of all your tasks, including assignees, status, and due dates, with ease. Plus, the Table view lets you quickly check on the progress of each project task.

But that’s not all—you can copy and paste your table into other software, pin specific columns, auto-size your columns, and even share your tables through a unique link.

This simple interface makes it a breeze to drag and drop rows and columns to quickly change your view.