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

Get FREE Advanced Excel Exercises with Solutions!

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.
=C6-D5

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.
=DATEDIF(C5,D5,"D")

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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo