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

### Category: Compare

How to find common values from two lists

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]Comments(13) Filed in category: Compare, Excel

Compare pricelists in excel 2007

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]Comments(7) Filed in category: Compare, Excel

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Comments(5) Filed in category: Compare, Excel, Unique distinct values

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]Comments(5) Filed in category: Compare, Excel

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Filter common values from three columns in excel

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]Comments(4) Filed in category: Compare, Excel

Compare and filter values between two lists in excel

Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. […]Comments(4) Filed in category: Compare, Excel

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]Comments(3) Filed in category: Compare, Excel