How to Add and Subtract Time in Excel (3 Suitable Examples)

One of the very popular uses of Excel is to calculate time and time differences. We may need to add or subtract time in Excel for a schedule or use custom time formats. In this article, we will learn about how to add and subtract time in Excel in multiple methods.

How to Add and Subtract Time in Excel: 3 Examples

There are 3 main uses of time calculation. We can calculate the time difference, add time & subtract time.

1. Subtract Time from Another Time

In this method, we will subtract and add time to one another. There are various ways and approaches through which we can do it. The methods are described with proper steps below.

1.1 Simple Subtraction

We can use simple subtraction to get the time difference or duration between two times. The steps are below.

Steps:

• First, we will have a simple data sheet like the image below that contains two times that we want to subtract. Here in the picture, we can see there are different projects having start and end times. So, we need to find out the duration of these projects.

• Second, we will select the cell where we want to see the duration. In our case, we will select cell E5.

• Third, in the formula bar, write the simple subtract formula of duration D5-C5 (End Time-Start Time) and press Enter.

• Then we will drag the Fill Handle to fill the rest of the cells.
• Finally, we will get the durations like the image below.

1.2 Using TIMEVALUE Function for Text Formatted Time

There is a dedicated TIMEVALUE function for calculating time. Here are the steps below. We will use the machine cycle here to get the results. The machine cycle assumes a day or 24 hours as 1. So, the time difference we will get will be in fractions.

Steps:

• Firstly, we will select the cells where we want to get the results. In our case, the cell range is E6 to G11.

• Secondly, we will press Ctrl+1 and select General, and press OK.

• Thirdly we will select E6 and write the following formula in the formula bar and press Enter.

• As a result, we will see the time difference in the machine cycle fraction. Now we will drag the Fill Handle to cover the rest of the cells.

The full machine time output will be like the below image.

• Now to get the hour difference from that fraction, we need to multiply the machine cycle with 24. So, we will select the cell F6 and write the following formula in the formula bar.

• Then press Enter.

• Again, drag the Fill Handle to get all the results.

All the durations in hours are shown in the image below.

• Similarly, if we want to get the results in minutes, we need to multiply the fractions with 24*60 or 1440. So, for minutes, we will select cell G6 and insert the following in the formula bar.

• Pressing Enter will give us the below result.

• Furthermore, we will drag the Fill Handle to fill up the rest of the cells to get results like the below image.

1.3 Combining HOUR, MINUTE & SECOND Functions with TIME Function

TIME function gives us the facility to calculate individual portions of time. The structure of the TIME function is TIME(hour, minute, second). So, we can take individual portions for calculation. The steps of this method are below.

Steps:

• At first, we will select the cell where we want our time duration. Here we will find time duration with the TIME function.

• Then we will write the following formula in the formula bar and press Enter.

• After pressing Enter we will get the result like the image below.

• At last, drag the Fill Handle to get results for the rest of the cells.

Read More: Adding and Subtracting in Excel in One Formula

1.4 Getting Time Difference in Hours, Minutes, or Seconds

Since Excel calculates the time difference in machine cycle time, we can get our data in any format as we need. The steps of this method are below.

Steps:

• In the beginning, we will find the difference in hours. So, we will select our desired cell and write the following formula in the formula bar.

Here ‘=(D6-C6)’ portion calculates the machine cycle time and the ‘*24’ portion gives us the time in Hours. The result we will get will be like the image below.

• Then we will drag the Fill Handle to fill the rest of the cells.

• Again, we will select a cell where we want the time difference in minutes. So, we will write the following formula in the formula bar.

Here 60 is multiplied because we want the result in minutes.

• Next, we will press Enter and drag the Fill Handle to get all the results in minutes.

• Furthermore, select another cell and insert the following formula in the cell to get the same duration in seconds.

• So again, drag the Fill Handle to get all the results like in the image below.

1.5 Get Time Difference in HH:MM:SS, HH:MM, etc. Formats Using TEXT Function

Using the TEXT function is another method to add or subtract time in Excel. But there is a difference in this method and other methods, while the other methods are time format based, for the TEXT method, the data needs to be in Plain Text format. The steps are below.

Steps:

• First, select the desired cell and write the following code in the cell.

Here the first portion will calculate the time in machine cycle time and the letter “h” will decide the unit.

• Then pressing Enter will give us a rounded-up result even if there is any fraction.

• Again, drag the Fill Handle for the rest of the cells.

• For getting the results in ‘hh:mm’ format, select another cell and write ‘=TEXT(D6-C6,”h:mm”)’ in the formula bar and press Enter. This will give us the result in the desired format.

• Drag the Fill Handle for the rest of the results.

1.6 Calculating Current Elapsed Time

When we are considering elapsed time from the starting time, we have to subtract the starting time from the current time. For that, we need to know the current time. Excel NOW function does give us the current date and time. So while we are subtracting the start time from the current time, we can actually get the elapsed time. So the steps are below.

Steps:

• First, select the cell where you want the elapsed time like the below image.

• Then write the following formula in the formula bar and press Enter. Here the C5 cell contains the starting time data. So, we are subtracting that time from the NOW() function’s time.

• Drag the Fill Handle for the rest of the cells for getting elapsed time.

2. Add or Subtract Hours/Minutes/Seconds to Time

Let’s say we want to add a limited time or duration to the existing time. Let’s say a company has multiple projects that start at different times but must be active for 8 hrs. So, we need to add 8 hours to the start time. And let’s say each project gets a break 2 hours prior to the ending time. So, we need to subtract 2 hrs from the ending time as well. So, the steps are below for that method.

Steps:

• Since we have the starting time, we will add 8 hours to it. So, we will use the TIME function in the desired cell (in our case the cell is D5) write the following formula, and press Enter. This will give us the ending time for project A.

• Then we will drag the Fill Handle for getting the End Time for the rest of the cells.

• Now for break we need to subtract 2 hrs from the End Time. So we will select the cell E5 and write the following code:

This will give us a time break for Project A.

• So, we will drag the Fill Handle for the rest of the project’s Break Time.

Read More: How to Add and Subtract Columns in Excel

3. Sum Several Times in Excel

Let’s say we have to find the total amount of time for different projects or work hours. The Durations are given like the following image. We need to find the total hours. So we will follow these simple steps:

• Select the cell where we will show the total hours and, in the formula bar, write-

Here the E5:E10 is the cell range that contains all the durations. And the 24 multiplier is for the hour’s conversion. For minute conversion, we will multiply 1440 instead of 24.

• After pressing Enter we will find the following result.

Read More: How to Add and Subtract in One Cell in Excel

How to Add and Subtract Military Time in Excel

Military time is another representation of time except there is no ‘:’ sign in between. The addition and subtraction of military time are exactly the same as other time intervals. So we will normally Subtract one time from another and for addition, we will use the TIME function. The method is shown in the steps below.

Steps:

• First, we need to calculate duration, or we will do subtraction. So, we will insert the following formula and press Enter.

• We will drag the Fill Handle like before to get the rest.

• Again, we want to add 1 hour to that duration. So, select a cell and enter the following formula in that cell. And press Enter.

• Pressing Enter will give us addition results like the following image.

• Dragging the Fill Handle will complete the cells for the rest of the projects.

Adding Time in Excel Over 24 Hours

When we are adding time that may cross the limit of 24 hours, Excel may show results like the image below.

Here the problem is the time format. So, to change the format, select the cell and then press Ctrl+1 > Custom > in the Type section write [h]:mm;@, and press OK. The [h] gives the total hour count.

The changed result is shown in the image below.

Read More: How to Add and Subtract Dates in Excel

Things to Remember

• If the time difference is negative, you may see ‘######’ error in the output box. So you should consider changing the time calculation method. Excel by default uses the 1900 time calculation format. By changing it to the 1940s we can avoid such errors. To do so, go to Files > Options > Advanced > When calculating this workbook and then select ‘Use 1904 date system’.

• Again, for complex calculations, consider including the date as well.
• The examples were made using Excel 365. So, the user interface may vary for different versions.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

That was all the methods to add and subtract time in Excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.

Related Articles

• How to Add or Subtract Based on Cell Value in Excel
• How to Add and Subtract Multiple Cells in Excel