This post demonstrates how we could find gaps in a sequential time list in excel.
By sequential time, I mean we have time readings at a regular interval in a column in excel we want to find the gaps in the sequential timing.
Below is the sequence of time that we have in column A in excel and we want to find the cells where there sequential gap.
Step 1
We will create a flag in column B which will highlight the value in column A which has a sequential time gap of 15 minutes in excel, you could take any number of minutes as per your requirement.
Enter the formula =IF(A3-A2>=TIME(0,15,0),”HERE”,””) in cell B3 to get the difference of time between cell A3 and A2, TIME function in excel will help us in formula to tell excel that we are looking for difference of 15 minutes and will put “HERE” just beside the cell where it would find the difference of more than 15 minutes.
Second argument of TIME function represents minutes and hence I have given 15 there.
Step 2
Now drag the formula till where you have the data available in column A.
As you could see, cell B13 has “HERE” which is telling us that the difference between the time values in cell A12 and A13 is more than 15 minutes.
As you saw, with the help of some formulae we could find the sequential time gap in excel.
You could change the TIME function in our formula based on your requirement e.g. if you want to find the difference of 20 seconds, you could use TIME(0,0,20).
Hope this helped.
