Question: How to identify two consecutive dates in a list?

Answer:

Array formula in cell B1:

How to enter an array formula

1. Select cell B1
2. Copy above formula (Ctrl + c)
3. Press with left mouse button on in formula bar
   
4. Paste formula (Ctrl + v)
5. Press and hold CTRL + SHIFT
6. Press Enter
7. Release all keys

Explaining array formula in cell B1

Step 1 - Check if next consecutive date is in cell range

IF((A1+1)=$A$1:$A$30,A1+1,"")

becomes

IF((39462+1)={39462; 39451; 39468; 39454; 39452; 39457; 39477; 39453; 39471; 39464; 39450; 39471; 39457; 39455; 39463; 39456; 39476; 39471; 39449; 39466; 39478; 39475; 39460; 39463; 39453; 39463; 39477; 39449; 39477; 39477},39462+1,"")

and returns

{""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 39463; ""; ""; ""; ""; ""; ""; ""; ""; 39463; ""; 39463; ""; ""; ""; ""}

Step 2 - Return the largest value

LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1)

becomes

LARGE(IF({""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 39463; ""; ""; ""; ""; ""; ""; ""; ""; 39463; ""; 39463; ""; ""; ""; ""},1)

and returns 39463 (2008-01-16) in cell B1.

Step 3 - Remove errors

If the LARGE function returns an error the IFERROR function removes that error. That happens every time there isn´t a following consecutive date.

Get excel *.xlsx file

identify-2-consecutive-dates-in-a-list-in-excelv3.xlsx

Functions in this post

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

LARGE(array, k)
Returns the k-th largest value in a data set.

Sequence category

Repeat values across cells
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Find the most/least consecutive repeated value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

Excel categories

Leave a Comment