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.

Overview image to calculate the duration of time 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.

Sample dataset containing the work time tracker

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.

Using the elementary formula in Excel

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

Inserting the Number Format to calculate the duration of time in Excel

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

Choosing the Custom format from the Format Cells

  • Belatedly, you will see the form you wanted.

The result of using the elementary formula to calculate the duration of time in Excel

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

The result after dragging the fill handle icon to calculate the duration of time in Excel

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.

Computing passed time in hours using Excel formula

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

Inserting the Number Format to calculate the duration of time in Excel

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

The result of passed time in hours

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.

Computing passed time in minutes using Excel formula

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

Inserting the Number Format to calculate the duration of time in Excel

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

The result of passed time in minutes

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.

Computing passed time in seconds using Excel formula

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

Inserting the Number Format to calculate the duration of time in Excel

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

The result of passed time in seconds

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.

Using the TEXT function to calculate the duration of time in Excel

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

How to Calculate Duration of Time in Excel

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.

Using the IF function to calculate the duration of time in Excel

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

The result of using the IF function to calculate the duration of time in Excel

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.

Using the HOUR function to calculate the duration of time in Excel

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

=MINUTE(D6-C6)

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

Using the MINUTE function to calculate the duration of time in Excel

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

=SECOND(D6-C6)

  • Press the Enter key.
  • Now you have got the second.

Using the SECOND function to calculate the duration of time in Excel

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

The result of using the HOUR, MINUTE, and SECOND functions

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.

Options from the File tab

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

Using the date system from the Excel Options

  • Again select cell E6 instantly.
  • Fourthly, put the formula there.

=C6-D6

  • Now press the Enter key.

Calculating the negative time in Excel

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

The result of calculating the negative time in Excel

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.

Using the SUM function to sum over 24 hours in Excel

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

Applying the Custom format from the Format cell

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

The result of using the SUM function to sum over 24 hours in Excel

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.

The result showing the hash (###) sign

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.

Applying IF, TEXT, and ABS functions

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

The result of using the IF, TEXT, and ABS functions

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.

Using the MOD function

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

the result of using the MOD function

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.

Using the TIME function

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

Using the TIME function

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.

Using the TIME function

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

Using the TIME function

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

(Visited 18 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo