How to calculate the time between two times in Excel

In this modern age, the use of MS Excel is vast. With its broad usage, people often need to calculate time durations for different activities. For this purpose, excel provides various functions and tools for the calculation of the time period between a starting and ending point. One can easily calculate accurate time duration in various formats, as per requirements, while using these features of Excel. These methods prove to be very convenient to use while handling a large set of data.

There are various methods to calculate time duration in different formats. In this tutorial, the two simplest and easiest methods are explained to evaluate time duration between a starting and ending point in three formats i.e. hours, minutes, and seconds.

METHOD 1 – Calculation of time duration using arithmetic operations

In this method, we’ll calculate the time duration between two given times by using simple arithmetic operations in hours, minutes, and seconds. Consider an example:

A scientist performed 8 experiments and noted their starting and ending times. He listed all the data in a spreadsheet and now he wants to calculate the time duration.

Consider the following data set.

STEP 1 – Formatting the data set

In the above data set, the starting and ending time is in standard format. To make the calculations convenient, we’ll need to format the cells containing the times.

• Select the columns starting with the heading “Start Time” and “Finish Time”.
• Right-click on it and a drop-down menu will appear.
• Select the “Format cells” option.

• A box will appear. Click on the ”Time” option.
• Select the format according to your requirements.
• The format of the columns will be changed.

STEP 2 – Create a column for the calculation of time duration

• Move your cursor to the first cell of the column in which you want to calculate the duration.
• Label the column with the heading “Time Duration”.

STEP 3 – Formatting the “Time Duration” Column

• Select the column till the cell in which duration is required.
• Right-click on it. A drop-down menu will appear.
• Click on the “Format Cells” option.
• Click on the “Time” option and then click on “13:30”, which is in “hh:mm” format.

STEP 4 – Apply the formula 

• Move your cursor to the first cell of the “Time Duration”.
• The required formula for the calculation of the time duration of “Experiment 1” is:

                                                         =C2-B2

• Write down the formula in the cell.
• Time duration will be calculated in “hh:mm” format.

STEP 5 – Calculate all durations using AutoFill Handle

• Move your cursor to the cell in which the time duration of Experiment 1 is calculated and select the cell.
• Move your cursor to the bottom right corner of the cell.
• Drag the cursor to the last cell in which you want to calculate the time duration.
• The time duration of all the experiments will be calculated automatically.

 METHOD 2 – Calculation of time duration using functions

Other than arithmetic operations, we can calculate time durations between a starting and ending point by using functions in Excel.

Consider the formatted data set as in “METHOD 1”.

Following are the steps to calculate time duration using functions in excel:

STEP 1 – Label a column for the calculation of time duration

• Point the cursor to the first cell of the column, next to “Finish Time”.
• Write down the heading “Time Duration” 

STEP 2 – Apply the formula 

• Select the first cell of the column “Time Duration”, in which you want to calculate the time duration of Experiment 1.
• The required formula for the calculation of the time duration of Experiment 1 is:

                                =TEXT(MOD(C2-B2,1),”hh:mm”)

• The time duration of Experiment 1 will be calculated

STEP 3 – Use AutoFill Handle to calculate the time durations of all experiments

• Move the cursor to the cell in which the duration of Experiment 1 is calculated.
• Point the cursor at the bottom right corner of the cell.
• Drag it down to the last cell in which the time duration of experiment 9 is to be calculated.
• The duration of all experiments will be evaluated.