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.

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.

=C6-D5

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.

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

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.

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.

=DATEDIF(C5,D5,"D")

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.

Calculate Lag Time.xlsx

Conclusion

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.

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

Get FREE Advanced Excel Exercises with Solutions!