Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates that are between date ranges.
I have also built a small calendar using conditional formatting to show exactly where the missing dates are. The formula below works fine with overlapping date ranges.
I will explain the formula in this article and there will also be a file for you to get.
What's on this webpage
1. Find empty dates in a set of date ranges - Excel 365
The "X" on row 6 shows which dates are empty or not overlapping the specified date ranges in cells B3:C5. The cells above show which dates each range covers, there are five dates not in any of the date ranges.
Excel 365 dynamic array formula in cell B8:
1.1 Explaining formula
Step 1 - Find the latest date
The MAX function calculate the largest number in a cell range.
Function syntax: MAX(number1, [number2], ...)
MAX(B3:C5)
becomes
MAX({43102, 43104; 43107, 43108; 43112, 43114})
and returns 43114. 43114 represents Excel date '1/14/2018'.
Step 2 - Find the earliest date
The MIN function returns the smallest number in a cell range.
Function syntax: MIN(number1, [number2], ...)
MIN(B3:C5)
becomes
MIN({43102, 43104; 43107, 43108; 43112, 43114})
and returns 43102. 43102 represents Excel date '1/2/2018'.
Step 3 - Calculate the difference in days between the earliest date and the latest date
The minus character lets you subtract numbers in an Excel formula.
MAX(B3:C5)-MIN(B3:C5)
becomes
43114-43102 equals 12.
Step 4 - Create a sequence of Excel dates
The SEQUENCE function creates a list of sequential numbers
Function syntax: SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5))
becomes
SEQUENCE(12,, 43102)
and returns
{43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113}.
Step 5 - Check if dates in sequence are larger or equal to the start dates
The less than and equal signs are logical operators that let you compare value to value, in this ,case if a number is smaller than or equal to another number.
B3:B5,"<="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5))
becomes
{43102;43107;43112},{"<=43102";"<=43103";"<=43104";"<=43105";"<=43106";"<=43107";"<=43108";"<=43109";"<=43110";"<=43111";"<=43112";"<=43113"}
Step 6 - Check if dates in sequence are smaller or equal to the end dates
The larger than and equal signs are logical operators that let you compare value to value, in this case, if a number is larger than or equal to another number.
C3:C5,">="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5))
becomes
{43104;43108;43114},{">=43102";">=43103";">=43104";">=43105";">=43106";">=43107";">=43108";">=43109";">=43110";">=43111";">=43112";">=43113"}
Step 6 - Apply AND-logic
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.
Function syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
COUNTIFS(B3:B5,"<="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),C3:C5,">="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)))
becomes
COUNTIFS({43102;43107;43112},{"<=43102";"<=43103";"<=43104";"<=43105";"<=43106";"<=43107";"<=43108";"<=43109";"<=43110";"<=43111";"<=43112";"<=43113"},{43104;43108;43114},{">=43102";">=43103";">=43104";">=43105";">=43106";">=43107";">=43108";">=43109";">=43110";">=43111";">=43112";">=43113"})
and returns
{1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1}.
Step 7 - Check if number is equal to 0 (zero) meaning no date range is overlapping
COUNTIFS(B3:B5,"<="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),C3:C5,">="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)))=0
becomes
{1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1}=0
and returns
{FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE}.
Step 8 - Filter no overlapping dates
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),COUNTIFS(B3:B5,"<="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),C3:C5,">="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)))=0)
becomes
FILTER({43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113},{FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE})
and returns
{43105; 43106; 43109; 43110; 43111}.
Step 9 - Shorten the formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
FILTER(SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),COUNTIFS(B3:B5,"<="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)),C3:C5,">="&SEQUENCE(MAX(B3:C5)-MIN(B3:C5),,MIN(B3:C5)))=0)
y : B3:C5
x : SEQUENCE(MAX(y)-MIN(y),,MIN(y))
LET(y, B3:C5, x, SEQUENCE(MAX(y)-MIN(y),,MIN(y)), FILTER(x,COUNTIFS(B3:B5,"<="&x,C3:C5,">="&x)=0))
2. Find empty dates in a set of date ranges - earlier versions
Array formula in cell B8:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Now copy cell B8 and paste as far as needed to cells below.
2.1 How to adjust cell references in the array formula to your worksheet
Cell range $B$3:$B$5 contains the start dates of the date ranges and $C$3:$C$5 contains the end dates.
$B$3:$C$5 contains both the start and end dates of your date ranges. Adjust these accordingly to your worksheet and don't forget to enter the formula as an array formula.
$A$1:A1 is only an expanding cell reference that lets the SMALL function extract the correct date value, you don't need to change it.
2.2 Explaining formula in cell B8
Step 1 - Find the earliest date
The MIN function returns the smallest earliest date from cell range $B$3:$C$5. The dollar signs make sure that the cell reference doesn't change when we copy the cell and paste it to the cells below.
MIN($B$3:$C$5)
becomes
MIN({43102, 43104; 43107, 43108; 43112, 43114})
and returns 43102.
Step 2 - Find latest date
The MAX function returns the lates date from cell range $B$3:$C$5
MAX($B$3:$C$5)
becomes
MAX({43102, 43104; 43107, 43108; 43112, 43114})
and returns 43114.
Step 3 - Concatenate results
The ampersand character lets you concatenate strings.
MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)
becomes
43102&":"&43114
and returns "43102:43114".
Step 4- Create a cell reference
The INDIRECT function converts a text string to a working cell reference.
INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))
becomes
INDIRECT("43102:43114")
and returns 43102:43114.
Step 5 - Create an array of row numbers
ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)))
The following formula returns an array of Excel dates needed to extract the missing dates.
ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)))
becomes
ROW(INDIRECT(43102&":"&43114))
becomes
ROW(43102:43114)
and returns
{43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113; 43114}.
Step 6 - which dates are outside the date ranges
The COUNTIFS function returns an array that we can use to extract dates not in date ranges. This particular COUNTIFS function has 4 arguments, however, you can use up to 255 arguments.
COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))
becomes
COUNTIFS($B$3:$B$5,{"<=43102"; "<=43103"; "<=43104"; "<=43105"; "<=43106"; "<=43107"; "<=43108"; "<=43109"; "<=43110"; "<=43111"; "<=43112"; "<=43113"; "<=43114"},$C$3:$C$5,{">=43102"; ">=43103"; ">=43104"; ">=43105"; ">=43106"; ">=43107"; ">=43108"; ">=43109"; ">=43110"; ">=43111"; ">=43112"; ">=43113"; ">=43114"})
and returns {1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1; 1}. This array tells us which dates is in the array and which are not. 1 - yes, 0 (zero) - no. The position in this array is important to identify the corresponding date.
Step 7 - Compare each value in array with 0 (zero)
Value 0 (zero) shows us that the corresponding date is not in the date range so I am now going to compare each value in the array to 0 (zero).
The equal sign lets you compare a value to an array of values, the result is a boolean value TRUE or FALSE.
COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0
becomes
{1; 1; 1; 0; 0; 1; 1; 0; 0; 0; 1; 1; 1}=0
and returns
{FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE}.
Step 8 - IF function returns an array of correct dates
The IF function uses the logical values to filter the dates we are looking for.
IF(COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0, ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))
becomes
IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE}, {43102; 43103; 43104; 43105; 43106; 43107; 43108; 43109; 43110; 43111; 43112; 43113; 43114})
and returns {FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}
Step 9 - Extract the k-th smallest number (date)
The SMALL function returns dates based on their sizes, the second argument uses an expanding cell reference so that the small function extracts the smallest value in cell B8 and the second smallest in cell B9 and so on.
SMALL(IF(COUNTIFS($B$3:$B$5, "<="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))), $C$3:$C$5, ">="&ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5))))=0, ROW(INDIRECT(MIN($B$3:$C$5)&":"&MAX($B$3:$C$5)))), ROWS($A$1:A1))
becomes
SMALL({FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}, ROWS($A$1:A1))
The ROWS function counts the number of rows in a given cell reference, the cell reference used here is a growing cell reference. It contains an absolute and a relative part indicated by the dollar signs.
becomes
SMALL({FALSE; FALSE; FALSE; 43105; 43106; FALSE; FALSE; 43109; 43110; 43111; FALSE; FALSE; FALSE}, 1)
and returns 43105 in cell B8.
Excel formats the number as a date and shows 1/5/2018, see picture below.
Dates category
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 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Missing values category
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
3 Responses to “Find empty dates in a set of date ranges”
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.
Excellent. Thanks.
I just had to say that the 'Find the missing dates' formula is beautifully elegant and can be adapted to any series in sequential order (numbers, text etc).
Thank you very much for this gem.
Hello
what do i do if i want to compare the dates i've found as a result of your excellent formula to another table of date range and filter them.
For example i have found 1/7/2014
2/7/2014
3/7/2014
31/12/2015
13/6/2015
19/9/2015
and i need to find out which dates are between start: end:
1/6/2013 12/5/2015
17/6/2015 31/12/2016
Thank you !