How to Calculate Lag Time in Excel (2 Suitable Methods)

This article illustrates how to calculate Lag Time in Excel. The lag time or time lag is a delay between activities when there should be none. You can easily calculate the time lag in Excel by subtracting the supposed and actual starting or ending times for the activities. Follow the article to learn how to do that.

What Is Lag Time?

Lag Time or Time Lag is a delay between two successive tasks, whereas the later task should start as soon as the earlier task ends. Suppose a project consists of 5 successive tasks. Each task should start as soon as the previous task ends. But each new task started after a delay due to mechanical failures and other difficulties. Then this delay itself is the time lag. You can sum up all the delays to get the total time lag. It will be equal to the difference between the projected deadline and the actual deadline of the project.

How to Calculate Lag Time in Excel: 2 Simple Methods

Here I will discuss how you can calculate the lag time in Excel with two simple examples.

Method 1: Calculate the Lag Time Between Consecutive Tasks

Assume you have the following dataset containing the start and end times of all tasks within a project. There is a delay between the start of each task and the end of the previous task. Now you need to calculate this lag time using Excel.

dataset 1

Follow the steps below to be able to do that.

  • First, enter the following formula in cell E6 and then drag the Fill Handle icon below.

formula for lag time between consecutive tasks

Here, C6 refers to the “start time” of task 2 whereas D5 is the “end time” of task 1.

  • Next, select the range E6:E13 and go to Home >> Format >> Format Cells or use the CTRL+1 shortcut.
  • Then, select the 37:30:55 format in the Time category from the Number tab and click OK.

format cells

  • Finally, you will get the lag time between the tasks as follows.

formatted lag time

Read More: How to Calculate Lead Time in Excel

Method 2: Calculate the Lag Time Between Shipment and End-Customer Sale

Suppose you need to calculate the time lag between “shipment” and “end-customer sales” of some products for your company.

dataset 2

  • Then you can use the DATEDIF function to create a formula in cell E5 as follows and drag the Fill Handle icon below. This will give the lag time in days.

calculate lag time using DATEDIF function

Things to Remember

  • You should subtract the earlier dates from the later dates to avoid negative results.
  • The DATEDIF function is not listed in Excel. Yet you can use it with the possibility of getting erroneous results in some scenarios.

Download Practice Workbook

You can download the practice workbook from the download button below.


Now you know how to calculate the lag time in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF