How to calculate duration in Google Sheets

When planning a project, estimating how long each task or event will take to complete is essential. By tracking the start and end of each event and calculating its duration, we can better understand how long the whole project will take and adjust our plans accordingly. By knowing how long each event will take, we can better allocate resources, such as people or equipment, to ensure they are being used effectively and efficiently. Tracking the time duration of events such as fitness goals, can help us to measure our performance and identify areas for improvement.

In today’s tutorial, we’ll learn how to calculate the duration of an event or a project in Google Sheets. Let’s consider the following scenarios or cases for calculating time duration:

Case 1 – Short Term Events

Let’s consider the following dataset that contains the details of “The Boston Marathon”.The dataset contains six runners, the time at which the marathon starts, and the time at which each runner finishes the race. Here the columns of start time and finish time are already formatted as time:

Step 1 – Create a new column named “Time to reach the finish line”

• Move your cursor to the first line of the next column.
• Give the heading to the column for “Time to reach the finish line”.

Step 2 – Change the format of the newly added column to “Time”

• Select the column named “Time to reach end line”.
• Click on the 123 button from the ribbon.
• Select “Duration” from the drop-down menu.

Step 3 – Use the subtraction formula for Time

• Create an appropriate formula for subtraction of “Finish Time” and “Start Time”.
• In this case, the formula is =C4-B4.
• Write the formula in the “Time to reach end line” column.
• Press enter key and it will produce the desired results as shown below.

Step 4 – Use the Fill handle to calculate the duration for all runners

• Move your cursor to the bottom right corner of the cell.
• Drag the Fill handle up to Runner 6.
• The time taken by each runner to complete the marathon will be calculated.

Case 2 – Long-term Events

Let’s consider the following dataset that contains the details of a housing society project, assuming the name of the society is “Eden housing society”. The dataset contains a number of events that take place during the construction of the housing society, and their starting and ending dates. In this particular dataset, the columns for “Starting Date” and “Ending Date” are already formatted as “Date”:

Step 1 – Create a column for “Event Duration”

• Move your cursor to the first cell of the next column.
• Give the heading for “Event Duration”.

Step 2 – Create a custom format for the Event Duration column

• Select the column for “Event Duration”.
• Click on the 123 button from the ribbon.
• Choose “Custom date and time” from the drop-down menu.
• Create an appropriate format in which you want the event duration to be.
• In this case, the used format is [Months] Months and [Days] days.
• Click on Apply.

Step 3 – Use the subtraction formula for the date

• Create an appropriate formula for the subtraction of starting and ending dates.
• Write the formula in the “Event Duration” column.
• Press enter key.

Step 4 – Use the Fill handle to calculate the duration of all events

• Move your cursor to the bottom right of the cell in which the formula has already been implemented.
• Drag the Fill handle up to “Handover”.
• This will calculate the duration for all events.

Step 5 – Calculate the duration of the whole project

• Create an appropriate formula for the subtraction of the first starting date entry from the last ending date entry.
• Write the formula in a cell.
• In this case, the formula is =D12-C5
• Change the format of the cell to “Custom date and time”, as shown above in step 2.
• Press enter.
• The duration of the whole housing society project will be calculated.