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.

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))

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

Get the Excel file

Find-missing-dates-in-a-set-of-date-rangesv2.xlsx

Back to top

Dates category

Create a date range [Formula]
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 the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Missing values category

Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

What values are missing in List 1 that exists i List 2?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Overlapping category

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 […]

Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range.  The second section shows how […]

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 […]

Excel categories

Comments (3)