Find all sequences of consecutive dates
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in random order.
The condition that must be met is that at least two dates is exactly one day a part from each other. Duplicate dates are repeated in the returned date sequence.
Array formula in cell D3:
This formula can be entered as a regular formula if you are an Excel 365 subscriber. Earlier versions must enter this formula as an array formula to make it work properly.
How to enter an array formula
- Copy array formula above (Ctrl + c).
- Doubleclick on cell D3.
- Paste formula (Ctrl + v)
- Press and hold CTRL + SHIFT simulateously.
- Press Enter once.
- Release all keys.
The formula now begins with and ends with a curly bracket, like this: {=array_formula}. Don't enter these characters yourself, they appear automatically if you followed the steps above.
How to copy array formula
- Select cell D3.
- Copy cell (Ctrl + c).
- Select cell range D4:D5.
- Paste (Ctrl + v).
The relative cell refences in the formula changes automatically when you copy the cell (not the formula) and paste to cells below.
Explaining the formula in cell D3
To understand formulas in greater detail I recommend you use the "Evaluate Formula" tool. Go to tab "Formulas" on the ribbon. Click the "Evaluate Formula" button and a dialog box appears.
Click the "Evaluate" button on the dialog box to show the next calculation step by step. Click "Close" button to dismiss the dialog box.
Step 1 - Find dates that have a consecutive date except for the last consecutive date
The COUNTIF function counts cells based on a condition, however, we are going to use multiple conditions. The COUNTIF function will, in this case, return an array with values equal to the number of conditions. Their position in the array matches the position of each condition.
COUNTIF(range, criteria)
The formula is working with dates so I now need to explain how Excel handles dates. Excel dates are actually regular numbers, number 1 is equal to 1/1/1900 and number 36526 is 1/1/2000. There are 36525 days between 1/1/2000 and 1/1/1900. Excel basically formats cells containing numbers as dates.
You can check this yourself, type 1/1/1900 in a cell. Select the cell and press CTRL + 1 to open the "Format Cells" dialog box. Click "General" and press the OK button. The selected cell now shows 1.
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}. This array tells us which dates have a consecutive date, however it will not tell us which date is the last consecutive date. The next step will take care of that.
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}. This arraray shows us which dates are the last consective date for any given sequence.
Step 3 - Add arrays
To identify all consecutive dates we must add both arrays meaning we are applying 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)
This step is actually not needed, the IF function will in the next step handle any number as the boolean value TRUE and 0 (zeros) as FALSE. There is really no need to convert the array to their boolean counterparts.
(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 - Filter consecutive dates
The IF function returns the corresponding date if the logical expression returns TRUE and a blank if FALSE.
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; ""}.
Step 6 - Find the k-th smallest date
The SMALL function extracts the k-th smallest number using the ROWS function and absolute and relative cell references in order to return values in a cell each.
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, ""),ROWS($A$1:A1))
becomes
SMALL({41430; ""; 41432; ""; 41431; ""}, ROWS($A$1:A1))
becomes
SMALL({41430; ""; 41432; ""; 41431; ""}, 1)
and returns 41430 (June 5, 2013) in cell D3. The SMALL function will sort the output dates from smallest to largest.
Conditional formatting consecutive dates
You can also highlight consecutive dates using Conditional formatting.
Conditional formatting formula:
How to apply conditional formatting
- Select cell range B3:B8.
- Go to "Home" tab on the ribbon.
- Click the "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.
- Click on the "Fill" tab to select it.
- Pick a color.
- Click OK button.
- Click OK button.
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
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:
Recommended articles
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
List all unique events in a month
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
3 Responses to “Find all sequences of consecutive dates”
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.
Very clever solution!
Thanks for sharing.
pmsocho,
Thank you for commenting!
I am wondering if there is a way to count the number of days between dates in consecutive rows of a spreadhseet in a pivot table. The use case is to find the number of days between human error incidents for a group of operators in a manufacturing environment. Simply put if you have col A as operator name(s), B as dates of human errors how would you count the days between human errors as an array? Any thoughts would be appreciated.