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

Answer: This formula checks if there are any consecutive dates in the list.

=IF(MIN(IF(ABS(A1-\$A\$1:\$A\$30)=1, ROW(\$A\$1:\$A\$30),""))=0,"",INDEX(\$A\$1:\$A\$30, MIN(IF(ABS(A1-\$A\$1:\$A\$30)=1,ROW(\$A\$1:\$A\$30),"")))) + Ctrl + Shift + Enter

MIN(IF(ABS(A1-\$A\$1:\$A\$30)=1,ROW(\$A\$1:\$A\$30),"")) This formula checks if there are any dates that are one day before or after (A1). If there are, return the row number. I then use the row number to get the consecutive date with  index().

Download excel sample file for this tutorial. identify-2-consecutive-dates-in-a-list-in-excel.xls (Excel 97-2003 Workbook *.xls)

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

ABS(number)
Returns the absolute value of a number, anumber without its sign.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

### Related posts:

Extract dates using a drop down list in excel

Create a list of dates with blanks between quarters in excel

Excel:Print consecutive pagenumbers from several sheets

List dates outside specified date ranges in excel

Most common value between two dates in a range in excel