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 (20080116) 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
identify2consecutivedatesinalistinexcelv3.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 kth 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 builtin 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