## How to identify two consecutive dates in a list

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

**Answer:**

**Array formula in cell B1:**

**How to enter an array formula**

- Select cell B1
- Copy above formula (Ctrl + c)
- Click in formula bar

- Paste formula (Ctrl + v)
- Press and hold CTRL + SHIFT
- Press Enter
- 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.

### Download 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.

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on […]

Find the longest/smallest consecutive sequence of a 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 show […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form