## Find consecutive dates in a cell range

Column B contains random dates. The array formula in column D returns consecutive dates from column B.

**Array formula in cell D3:**

**How to enter an array formula**

- Select cell D3
- Copy array formula (Ctrl + c)
- Click in formula bar
- Paste formula (Ctrl + v)
- Press and hold CTRL + SHIFT
- Press Enter simultaneously
- Release all keys

**How to copy array formula**

- Select cell D3
- Copy (Ctrl + c)
- Select cell range D4:D5
- Paste (Ctrl + v)

### Find consecutive numbers

Since dates are numbers in excel you can also use the array formula to identify consecutive numbers.

### Find non-consecutive dates

**Array formula in cell D3:**

### Explaining the array formula in cell D3 (Consecutive dates)

**Step 1 - Find dates that have a consecutive date except the last consecutive date**

COUNTIF($B$3:$B$8,$B$3:$B$8+1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41430; 41436; 41432; 41441; 41431; 41438}+1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41431; 41437; 41433; 41442; 41432; 41439})

and returns

{1;0;0;0;1;0}

If you are interested in how the COUNTIF function works, read this post: COUNTIF function

**Step 2 - Find dates that have a consecutive date except the first consecutive date**

COUNTIF($B$3:$B$8,$B$3:$B$8-1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41430; 41436; 41432; 41441; 41431; 41438}-1)

becomes

=COUNTIF({41430; 41436; 41432; 41441; 41431; 41438}, {41429; 41435; 41431; 41440; 41430; 41437})

and returns

{0;0;1;0;1;0}

**Step 3 - Add arrays using OR logic**

COUNTIF($B$3:$B$8,$B$3:$B$8+1)+COUNTIF($B$3:$B$8,$B$3:$B$8-1)

becomes

{1;0;0;0;1;0} + {0;0;1;0;1;0} and returns {1;0;1;0;2;0}

**Step 4 - Find values in array larger than 0 (zero)**

(COUNTIF($B$3:$B$8,$B$3:$B$8+1)+COUNTIF($B$3:$B$8,$B$3:$B$8-1))>0

becomes

{1;0;1;0;2;0}>0

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}

**Step 5 - Replace TRUE with corresponding date and FALSE with blanks**

IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, $B$3:$B$8, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {41430; 41436; 41432; 41441; 41431; 41438}, "")

and returns {41430; ""; 41432; ""; 41431; ""}

Interested in how the IF function works, read this post: IF function explained

**Step 6 - Find the n-th smallest date**

SMALL(IF((COUNTIF($B$3:$B$8, $B$3:$B$8+1) + COUNTIF($B$3:$B$8, $B$3:$B$8-1))>0, $B$3:$B$8, ""),ROW(A1))

becomes

SMALL({41430; ""; 41432; ""; 41431; ""}, ROW(A1))

becomes

SMALL({41430; ""; 41432; ""; 41431; ""}, 1)

and returns 41430 (June 5, 2013) in cell D3.

Interested in how the SMALL function works, read this post:

SMALL function and LARGE function

The ROW function: ROW function explained

### Conditional formatting consecutive dates

**Conditional formatting formula:**

=(COUNTIF($B$3:$B$8,B3+1)+COUNTIF($B$3:$B$8,B3-1))>0

**How to apply conditional formatting**

- Select cell range B3:B8
- Go to "Home" tab
- Click "Conditional formatting" button
- Click "New Rule..."
- Click "Use a formula to determine which cells to format"
- Format values where this formula is TRUE:

=(COUNTIF($B$3:$B$8,B3+1)+COUNTIF($B$3:$B$8,B3-1))>0 - Click "Format.." button
- Go to "Fill" tab
- Pick a color
- Click OK
- Click OK

Read more about conditional formatting:

- Working with formulas in conditional formatting
- Search with conditional formatting
- How to use a table name in data validation lists and conditional formatting formulas

### Download excel *.xlsx file

### Functions in this post

**COUNTIF(***range*, *criteria***)**

Counts the number of cells within a range that meet a single criterion that you specify.

**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

**SMALL(***array*, *k***)
**Returns the k-th smallest value in a data set

**ROW(**reference**)**

Returns the row number of a reference.

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(82) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) How to enter an array formula Select cell E3 Type or copy/paste above […]Comments(40) Filed in category: Dates, Excel

Highlight overlapping date ranges using conditional formatting in excel

How to highlight overlapping date ranges (Excel 2007) Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Count records between two dates in excel

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]Comments(22) Filed in category: Count values, Dates, Excel

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

### 2 Responses to “Find consecutive dates in a cell range”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Very clever solution!

Thanks for sharing.

pmsocho,

Thank you for commenting!