Sometimes we need to calculate the duration of time in **Excel**. You need to subtract the starting time from the ending time to compute the time duration in **Excel. **

Suppose, there is a company where the authority maintains the work time tracker of the employees. The **Work Time Tracker **contains the **Entry Time**, the **Exit Time**, and the **Working Duration** of the employees. Here entry and exit times are given. Using these two times we will calculate the duration of their working time in **Excel**. We have utilized the basic formula, the **TEXT **function, the **IF **function, the **HOUR **function, the **MINUTE** function, the **SECOND** function, etc. to get the result of this calculation in **Excel**.

## πΒ Download Excel File

Download the free Excel workbook from here:

## Learn to Calculate Duration of Time in Excel with These 7 Examples

While writing this article, weβve considered a dataset that contains approximately **5** columns and **11** rows. In this dataset, we added **Name**, **Entry Time**, **Exit Time**, and **Working Duration** to make you better understand. With the help of these data, we will perform our required operation. But if you want, you can change the entities and put your own values.

**EXAMPLE**

### 1. Using Elementary Formula and Custom Formatting

When it comes to calculating the duration of time in **Excel**, we will show you the elementary formula. This formula is very simple. You need to subtract the **Entry Time **from the **Exit Time**. After that, you will change the format of the **Working Duration **in your desired form. Follow the given steps.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Initially, select cell
**E6**. - Secondly, write the following formula on that cell.

**=D6-C6**

- Thirdly, press the
**EnterΒ**key. - This format is not our desired format. So, we will use the
**Custom**format to change the form of our result.

- Again select cell
**E6**. - Fourthly, go to the
**HomeΒ**tab. - Select the
**Number**feature and then click on the**Number FormatΒ**option.

- After that, you will find a dialogue box name
**Format Cells**. - Fifthly, select the
**Custom**format and put the**Type**:

**h:mm**

- Finally, click on the
**OKΒ**button.

- Belatedly, you will see the form you wanted.

- Afterward, use the
**fill handle**to drag the formula to the entire column. - Consequently, you will find your required result as well.

Undoubtedly, this process is very handy to calculate time duration in **Excel**.

**π Read More: ****6 Approaches to Subtract Time in Excel and Show Negative**

**EXAMPLE**

### 2. Computing Time Difference in Hours

In the previous section, we performed an elementary formula to calculate time duration in **Excel **containing the whole time including hours and minutes. In this example, we will compute the passed times in the **Hour** form individually. We will apply a very simple formula to achieve the operation. You just need to maintain the following instructions to do this.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Primarily, you need to select cell
**E6**. - Secondly, put the following formula on that cell.

**=(D6-C6)*24**

- Thirdly, you need to press the
**EnterΒ**key. - This format is not the format we want. So, we will use the
**Number**format to change the form of our result.

- Again you need to select cell
**E6**. - Fourthly, go to the
**HomeΒ**tab. - Select the
**Number**feature and then click on the**NumberΒ**option.

- Afterward, you need to use the
**fill handle**to drag the formula to the entire column. - Finally, the desired result will appear to you.

Truly, you can achieve this method without facing any difficulties calculate time duration in **Excel**.

**π Read More: ****9 Tricks to Calculate Time Difference Between AM and PM in Excel**

**EXAMPLE**

### 3. Calculating Spent Time in Minutes

Sometimes users may want to calculate time duration in minutes in **Excel**. We have evaluated the time duration through **Hours** in the above part. But now we will compute the duration of time with more accuracy which is in the **Minute** form. So, we will utilize a little **Excel **formula that will give us the desired result we want. You need to perform the following steps as well.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- First, you have to select cell
**E6**from your**Excel** - Secondly, you have to put the given formula there.

**=(D6-C6)*24*60**

- After that, tap the
**EnterΒ**key. - Like before, we will use the
**NumberΒ**feature.

- Afterward, you have to select cell
**E6**. - Fourthly, just go to the
**HomeΒ**tab. - Select the
**Number**feature and then click on the**NumberΒ**option.

- Belatedly, use the
**fill handle**and drag the formula to the entire column. - Finally, you will get the result you wanted.

Indeed, calculating time duration in the **Minute** form in **Excel**, this method is very simple to achieve.

**π Read More: ****5 Ways to Calculate Total Hours Worked in a Week in Excel**

**EXAMPLE**

### 4. Evaluating Passed Time in Seconds

Let, you are not satisfied with the previous result we obtained by calculating time duration in the **Minute** form in **Excel**. You want it more specifically. You want to have the duration of time in the **Second** form. We can do it easily. This time we will again apply an **Excel **formula to obtain the time duration in the **Second** form. Stick to the given steps.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- First, you need to choose cell
**E6**. - Secondly, put the given formula on that cell.

**=(D6-C6)*24*60*60**

- Thirdly, tap the
**EnterΒ**key. - Like before, we need to change the format.

- So, you have to select cell
**E6**. - Fourthly, you just go to the
**HomeΒ**tab. - Select the
**Number**feature and then click on the**NumberΒ**option.

- Later on, you need to use the
**fill handle**to drag the formula to the entire column. - Finally, your desired result will come in front of you.

Thus, this technique is one of the most accessible techniques to obtain the formula.

**π Read More: ****How to Calculate Turnaround Time in Excel Excluding Weekends**

**EXAMPLE**

### 5. Applying TEXT Function to Calculate Time Duration in Excel

Applying **Excel **functions to solve any kind of **Excel-**related work can reduce your workload easily. Remembering this thing, we are going to use the **TEXT **function to calculate the duration of time which is one of the simplest methods in **Excel**. We havenβt used any functions in the previous sections, so this is gonna be a unique one we hope. Try to follow the given steps consecutively.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Primarily, you need to select cell
**E6**. - Afterward, you have to put the following formula there.

**=TEXT(D6-C6,βhh:mm:ssβ)**

- Belatedly, press the
**Enter**key as well.

- After that, use the
**fill handle**and drag the formula to the whole column. - Eventually, you will see your result in an
**hour**,**minute**, and**secondΒ**form.

Truly, you can achieve this Example without facing any problems.

**EXAMPLE**

### 6. Using IF Function to Compute Duration of Time in Excel

In the above section, we have used the **TEXT **function to calculate the time duration in **Excel**. Now we are going to use the **IF **function to do the same thing. The **IF **function is basically used when we need to apply any criterion or condition. Two outcomes can come from an IF expression. If your evaluation is **True**, the first outcome is what you get; if itβs **False**, the latter. You need to follow the given steps to obtain the method.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Initially, select cell
**E6**. - Afterward, you have to put the following formula there.

**=IF(D6>C6, D6-C6, C6-D6)**

- Thirdly, you need to tap the
**EnterΒ**key.

- Belatedly, use the
**fill handle**icon and tug the formula to the entire column. - Finally, you will see the result as well.

Indeed, using this function to calculate the duration of time is very handy to achieve.

**EXAMPLE**

### 7. Calculating Time Duration Using Different Units in Excel

In all the above sections, we have calculated the duration of time synchronously (hours, minutes, and seconds) in **Excel**. Using the **TEXT **or **IF **function was enough to do this. But this time, we will calculate each hour, minute, and second individually. It will provide more accuracy in our calculation. We will utilize the **HOUR **function, the **MINUTE **function, and the **SECOND **function separately to obtain the result. So, letβs get started with the following steps.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- First, select cell
**E6**. - Secondly, put the formula there.

**=HOUR(D6-C6)**

- Tap the
**EnterΒ**key. - Now you have got the hour.

- After that, select cell
**F6**. - Then put the formula there.

**=MINUTE(D6-C6)**

- Tap the
**EnterΒ**key. - Now you have got the minute.

- Afterward, select cell
**G6**. - Then put the formula there.

**=SECOND(D6-C6)**

- Press the
**EnterΒ**key. - Now you have got the second.

- Belatedly, use the
**fill handle**and drag the formulas. - Finally, your desired result will appear to you.

Truly, this is a very handy Example to work on.

## How to Calculate and Display Negative Times in Excel

In the preceding sections, we have computed the time duration in **Excel **which was displayed in positive form. But now we want to do something different. We want to calculate and display negative times in **Excel**. This is not a difficult task. You can do it easily if you carefully maintain the following steps as well.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- First, go to the
**FileΒ**tab. - Secondly, click on
**Options**.

- Now you will get a dialogue box name
**Excel Options**. - Afterward, select the
**Advanced**option and then check the**Use 1904 date system**. - Then click on the
**OKΒ**button.

- Again select cell
**E6Β**instantly. - Fourthly, put the formula there.

**=C6-D6**

- Now press the
**EnterΒ**key.

- Sixthly, you need to utilize the
**fill handle**to drag the formula to the whole column. - Consequently, the result will appear to you.

Undoubtedly, this technique is one of the basic techniques to calculate and display negative times in **Excel**.

## How to Sum Over 24 Hours in Excel

When we try to sum up the total time, sometimes it crosses 24 hours or more than the time period of 1 day. But in **Excel**, you can not do this sum straightforwardly because **Excel **is not accustomed to this procedure unless you put in any special criterion. In this section, we are going to discuss how to sum over 24 hours in **Excel**. We will use the **SUM** function and the **Absolute Cell Reference **to obtain the method. Stick to the given steps accordingly.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Initially, select cell
**D**. - Secondly, write the following formula on that cell

**=SUM($C$6:$C$11)**

- Thirdly, press the
**EnterΒ**key. - Now we will change the time to our desired format.

- Fourthly, press
**Ctrl**+**T**. - Now you will get a dialogue box name
**Format Cells**. - Choose the
**Custom**option and then write for the**Type:**

**[h]:mm:ss**

- Lastly, click on the
**OKΒ**button.

- Eventually, you will get the result you wanted to have.
- As you see that the result contains a value that is more than 24 hours, so we have achieved our goal.

Finally, you must admit that the above procedure is very simple and handy to sum over 24 hours in **Excel**.

**π Read More: ****5 Easy Ways to Fix When SUM Time Is Not Working in Excel**

## How to Fix When Result is Showing (###) Hash Instead of Time

Sometimes you may face a very common problem while calculating time duration in **Excel**. This problem is β sometimes when you attempt to calculate the duration of time in **Excel**, you get the **Hash** (**###**) sign instead of the proper result. This problem occurs when you give incompatible formula without even changing the format. If you observe the below picture, you will understand the whole matter easily. Here the inserted formula is not familiar with the general condition in **Excel**. Thatβs why you see the **Hash **(**###**) sign instead of the appropriate result.

To fix this problem, you need to maintain the following steps.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Initially, select cell
**E6**. - Afterward, you have to put the following formula there.

**=IF(C6-D6>0, C6-D6, TEXT(ABS(C6-D6),βh:mmβ))**

**π¨ Formula Breakdown**

Here:

πΒ **ABS(C6-D6) **refers that the **ABS** function will return the value of **C6-D6** without any sign.

πΒ **TEXT(ABS(C6-D6),βh:mmβ) **provides that by using the **ABS** function to style it, the **TEXT** function allows you to adjust the value of **C6-D6**. It will present the data in a more concise way, which is the hour and minute format that we like.

πΒ **IF(C6-D6>0, C6-D6, TEXT(ABS(C6-D6),βh:mmβ)) **means the **IF **function will give the criterion of **C6-D6>0 **before showing the output as well.

- Press the
**EnterΒ**key.

- Belatedly, use the
**fill handle**icon to tug the formula to the entire column. - Finally, you get the desired result as well.

Indeed, if you want to fix the problem of showing the **Hash** (**###**) sign, you can easily follow the above method.

## How to Calculate Hours Between Two Times After Midnight in Excel

If you want to calculate hours between two times after midnight in **Excel**, then you have come to the right place. Let, you have gone to a place at **8:30 AM **and returned from there the next day at **5:50 AM**. Now you want to compute the hours between those two times after midnight. To do this easily, we will use the **MOD **function here. You just need to maintain the following steps.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Primarily, you need to select cell
**E6**. - Secondly, you have to put the following formula there.

**=MOD(D6-C6,1)**

- Thirdly, press the
**EnterΒ**key.

- Belatedly, use the
**fill handle**icon to drag the formula to the entire column. - Finally, you get the desired result as well.

Truly, this process is very unique to calculate the hours between two times after midnight in **Excel**.

**π Read More: ****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends**

## How to Add or Subtract Time in Excel

Suppose, some employees work in a firm and they have to work there for at least 9 hours. Today they have worked for **9 **hours** 35 **minutes** 20 **seconds. Now you want to compute their **Exit Time**. This computation is not very problematic. We will use the **TIME **function to add or subtract time in **Excel**. Maintain the given instructions to achieve the method.

β¬οΈβ¬οΈ **STEPS** β¬οΈβ¬οΈ

- Initially, go to your
**ExcelΒ**key. - Secondly, select cell
**D6**. - Thirdly, put the given formula on that cell.

**=C6+TIME(9,35,20)**

- Fourthly, tap on the
**EnterΒ**key.

- Now use the
**fill handle**and it will drag the formula to the entire column. - Consequently, you will find the result as well.

Again, let all of the employees have worked less than **35 **minutes of **9 **hours. Now you want to evaluate their **Exit Time**.

- First, select cell
**D6**. - Secondly, put the given formula on that cell.

**=C6+TIME(9,-35,20)**

- Tap the
**EnterΒ**key.

- Afterward, drag the formula down.
- Eventually, this is your required result.

Undoubtedly, this is a very simple method to add or subtract time in **Excel**.

## π Important Notes

You should be aware of the following things while performing the processes mentioned above:

ποΈΒ You need to be careful while using the **Excel **functions.

ποΈΒ You should practice the following functions before applying them as well.

## π Takeaways from This Article

If we summarize the whole article, we have got some points.

πΒ Initially, we used the elementary formula to calculate the duration of time in **Excel**.

πΒ Secondly, we computed the passed time in hours in **Excel**.

πΒ Thirdly, we calculated the spent time in minutes.

πΒ Fourthly, we evaluated the passed time in seconds.

πΒ Fifthly, we applied the **TEXT **function.

πΒ Sixthly, we adopted the **IF **function to calculate the duration of time in **Excel**.

πΒ Seventhly, we calculated time duration using different units.

πΒ Consecutively, we tried to calculate the negative times in **Excel**.

πΒ After that, we summed over 24 hours in **Excel**.

πΒ Simultaneously, we tried to fix the output when it shows the hash (###) sign instead of time.

πΒ Belatedly, we calculated hours between two times after midnight in **Excel**.

πΒ Finally, we added or subtracted time in **Excel**.

## Conclusion

We wish that you would be able to calculate the duration of time in **Excel** using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****4 ways to Create Excel Formula for Overtime Over 8 Hours****2 Ways to Calculate Travel Time Between Two Cities in Excel**