How to identify two consecutive dates in a list
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
Recommended categories
