How to Calculate the Duration of Time in Excel (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to know how to calculate the duration of time in Excel, you will find this article helpful. In this article, I’ve discussed 7 different ways to calculate time.

Suppose in your dataset, starting times and ending times are given. Now, I’ll show you how you can measure time durations from the starting times to the ending times.

dataset


1. Applying Excel Formula to Calculate Time Duration

You can calculate time duration in Excel with a simple subtraction formula. First,

➤ Type the following formula in cell D5,

= C5-B5

The formula will subtract the time of cell B5 from the time of cell C5. As a result, you will get the duration in cell D5.

subtraction

➤ Press ENTER.

You’ll see the output will be displayed in a time format, which is inappropriate in this case. You want to know the duration that means the difference between the two times. In time duration A.M./P.M. doesn’t make any sense. So, you have to change the time format.

result

➤ Select More Numbers Formats in the box of the Number ribbon of the Home tab.

time format

It will open the Number tab of the Format Cells box.

➤ Select a suitable time format from the Type box and click on OK.

In this demonstration, I’ve selected the h:mm format.

After that, you will find the duration between the times of cell B5 and C5 in cell D5.

how to calculate duration of time in excel

➤ Drag cell D5 to the end of your data set.

As a result, you will get all the time durations.

how to calculate duration of time in excel

If you observe carefully, you will see the duration in cell D9 showing 8 hours and 19 minutes. But the starting time and the Ending time are 10:00 AM and 6:20 PM. So, the duration should be 8 hours 20 minutes.

It is happening because the time in cell B9 has a second value that is not being displayed.

If you select the cell, you will see in the formula bar that there is a value in the “second” argument of the TIME function.

how to calculate duration of time in excel

You can display these second values.

➤ Select Time in the box of the Number ribbon of the Home tab.

time format

As a result, you will see the second value is displaying in all the time inputs.

time format


2. Inserting Excel TEXT Function to Calculate Duration of Time

With the TEXT function, you can calculate time duration in your desired format. First,

➤ Type the following formula in cell D5,

=TEXT(C5-B5,"hh:mm:ss")

The formula will first calculate the time duration by subtracting the time of cell B5 from the time of cell C5 and then it will show the duration in hh:mm:ss format.

text function

➤ Press ENTER.

As a result, you will get the time duration in cell D5.

time

➤ Drag cell D5 to the end of your data set.

As a result, you will get all the time durations in hh:mm:ss format.

how to calculate duration of time in excel

Read More: How to Calculate Total Hours in Excel


3. Calculating Elapsed Time in Hours

Excel assigns the number 1 to the 24 hours of a day. So 00:00 AM/ 12:00 PM has a value of 0 and 11:59 PM has a value of 0.999. Any time between these gets a fraction of 1. So, when you multiply the time difference by 24, you will get the time duration in hours.

In this method, I’ll show you how you can calculate the duration of time in Hours. First,

➤ Type the following formula in cell D5,

= (C5-B5)*24

The formula will subtract the time of cell B5 from the time of cell C5 and multiply the value by 24. As a result, you will get the duration in cell D5.

hour

➤ Press ENTER.

You will get the result in time format, so you have to change the format to Number to get the time duration.

how to calculate duration of time in excel

➤ Select Number in the box of the Number ribbon of the Home tab.

time format

As a result, you will get the time duration in hours.

how to calculate duration of time in excel

Now,

➤ Drag the cell D5.

As a result, you will get all the time durations.

how to calculate duration of time in excel

Read More: How to Sum Time in Excel


4. Getting the Duration of the Time in Minutes

You can also get the time duration in minutes. First,

➤ Type the following formula in cell D5,

= (C5-B5)*24*60

The formula will subtract the time of cell B5 from the time of cell C5 and multiply the value by 24 and 60. As a result, you will get the duration in cell D5.

minute

➤ Press ENTER.

You will get the result in time format, so you have to change the format to Number to get the time duration

result

➤ Select Number in the box of the Number ribbon of the Home tab.

time format

As a result, you will get the time duration in minutes.

how to calculate duration of time in excel

Now,

➤ Drag the cell D5.

As a result, you will get all the time durations.

how to calculate duration of time in excel


5. Calculating Time Duration from Start Time to Now

Time duration from a past time to now can be determined by Excel by using the NOW function.

Suppose, you have some past time in your dataset and you want to find out time duration from that time to the present moment. To do that

➤ Type the following formula,

= NOW () - B5

The NOW function will give the present time and the formula will give the time duration from the time in cell B5 to the present time.

now function

After that,

➤ Press ENTER.

You will get an output in time format with AM/PM. So, you need to convert the output to a suitable format.

how to calculate duration of time in excel

➤ Select More Numbers Formats in the box of the Number ribbon of the Home tab.

time formaat

It will open the Number tab of the Format Cells box.

➤ Select a suitable time format from the Type box and click on OK.

In this example, I’ve selected the h:mm:ss format.

format cells

As a result, you will get the time duration from the past time to present moment.

time

Now,

➤ Drag cell C5 to the end of your dataset.

So, you will get time durations for all the times of your dataset.

how to calculate duration of time in excel


6. Using Excel IF Function to Calculate Duration From Earlier to Former Time

In all the previous methods we have subtracted an earlier time from a former time. When we subtract a former time from an earlier time, Excel gives an irrelevant value, because Excel considers both the time of the same date. We can solve the problem by using the IF function in our formula.

Suppose, we have the following dataset, where some of the activities have ended in the next day of starting day.

dataset

To find out the time duration for this dataset,

➤ Insert the following formula in cell D5,

=IF(C5>B5, C5-B5, B5-C5)

The formula will subtract B5 from C5 if C5>B5. Otherwise, it will subtract C5 from B5. So, we will get the time duration irrespective of which one is earlier or former.

if function

➤ Press ENTER.

As a result, you will get the output in number format. So, you need to change it to a suitable time format.

➤ Select More Numbers Formats in the box of the Number ribbon of the Home tab.

time format

It will open the Number tab of the Format Cells box.

➤ Select a suitable time format from the Type box and click on OK.

In this example, I’ve selected the h:mm:ss format.

format cells

As a result, you will get the time duration for the time of cells B5 and C5 in cell D5.

how to calculate duration of time in excel

After that,

➤ Drag cell D5 to the end.

So, you will get the durations for all of the time intervals.

how to calculate duration of time in excel


7. Calculating Duration of Different Units of Time in Excel

You can also measure the time duration of different units from two different time inputs.

Let’s say, you have the following dataset where you have starting time and ending tie of some activities. Now, you want to know the time duration of hours, minutes and seconds of those activities.

how to calculate duration of time in excel

You can find out the hour duration by using the HOUR function.

➤ Type the following formula in cell D5,

=HOUR(C5-B5)

The formula will give the difference of the hour units of the times in cells C5 and B5. So, you will get the hour duration.

how to calculate duration of time in excel

➤ Press ENTER.

As a result, the hour duration will appear in cell D5.

how to calculate duration of time in excel

You can find out the minute duration by using the MINUTE function.

➤ Type the following formula in cell E5,

=MINUTE(C5-B5)

The formula will give the difference of the minute unit of the times in cells C5 and B5. So you will get the minute duration.

how to calculate duration of time in excel

➤ Press ENTER.

As a result, the minute duration will appear in cell E5.

how to calculate duration of time in excel

You can find out the second duration by using the SECOND function.

➤ Type the following formula in cell F5,

=SECOND(C5-B5)

The formula will give the difference of the hour units of the times in cells C5 and B5. So you will get the second duration.

how to calculate duration of time in excel

➤ Press ENTER.

So, you will get the second duration of the two times of cells B5 and C5 in cell F5.

how to calculate duration of time in excel

Now,

➤ Drag cell D5 to the end of your dataset.

As a result, you will get all the hour durations.

how to calculate duration of time in excel

Similarly, you can get the minute and second durations for all of the times by dragging the cells E5 and F5 to the end of your dataset respectively,

how to calculate duration of time in excel


Download Practice Workbook


Conclusion

In this article, I have demonstrated 7 methods to determine time duration in Excel which you can use to find the time duration in different conditions and in different units. I hope now you know how to calculate duration of time in Excel and will be able to apply the methods to your needs. If you have any confusion, please feel free to leave a comment.


Related Articles


<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo