7 Examples to Calculate Duration of Time in Excel

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.

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.

• 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:

• 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

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.

• 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

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.

• 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

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.

• 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

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.

• 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.

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.

• 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.

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.

• Tap the Enter key.
• Now you have got the hour.

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

• Tap the Enter key.
• Now you have got the minute.

• Afterward, select cell G6.
• Then put the formula there.

• 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.

• 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

• 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:

• 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.

• 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.

• 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.

• 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.

• 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