Find Missing Sequential Dates in a List in Google Sheets

GiGiKing·March 16, 2024·0 comments

I have a little secret to share with you! Are you ready to learn how to find missing sequential dates in a list in Google Sheets? Well, buckle up because I’m about to reveal an array formula that will make your life so much easier.

The Magic Array Formula

I have a preference for using array formulas whenever possible to simplify tasks in Google Sheets. And when it comes to finding missing sequential dates or numbers in a list, I’ve got just the formula for you.

Let’s say you have a list of dates in column A in Google Sheets. Whether these dates are ordered in sequential order or not, our magic formula will find the missing dates for you.

To make things crystal clear, take a look at this example:

Formula to Find Missing Sequential Dates in Google Sheets

How to Find Missing Sequential Dates in Google Sheets

Sounds intriguing, right? Let me break down the formula for you.

The array formula I used in cell C2 will list all the missing sequential dates.

Here’s the master formula:

=LET( range, A2:A, seq, SEQUENCE(DAYS(MAX(range),MIN(range))+1,1,MIN(range),1), FILTER(seq,IFNA(XMATCH(seq,range))="") )

Now, let me explain it step by step:

  • range: This is the range of cells that contains the list of dates.
  • seq: It represents the list of all possible dates between the first and last dates in the range.
  • formula_expression: This part filters (extracts) the missing dates from the list of possible dates.

You simply need to specify the range containing the list (e.g., A2:A), and the formula will take care of the rest. Easy peasy, right?

Remember, this is an array formula, so it will cover all the dates in the list in column A and populate the missing sequential dates in column C.

No need to copy and paste the formula to the cells below. Just enter the array formula in cell C2. However, make sure the cell range C2:C is blank before entering the formula.

Formula Explanation

Let me walk you through the different parts of the formula.

Generating Sequential Dates Based on Start and End Dates in the List: seq Part

We use the following SEQUENCE formula (seq) to generate a list of sequential dates from the minimum date to the maximum date in the range.

=SEQUENCE(DAYS(MAX(range),MIN(range))+1,1,MIN(range),1)

In simpler terms:

  • The rows argument specifies the number of values to return. It’s equal to the number of days between the minimum and maximum dates in A2:A.
  • The columns argument is optional and set to 1 to have the sequential dates in one column.
  • The start argument specifies the starting value, which is the minimum date in the range A2:A.
  • The step argument is optional and set to 1 to increase the dates by 1.

We then compare this sequence with the range A2:A to extract the mismatching values, which are the missing sequential dates in the list.

Filter Missing Sequential Dates Comparing Two Lists: formula_expression Part

To filter the missing sequential dates, we use the following FILTER formula (formula_expression) that compares two lists.

=FILTER(seq,IFNA(XMATCH(seq,range))="")

Let me explain it further:

We compare two lists in the formula. List 1 (seq) is the sequence of dates generated by the SEQUENCE function, and List 2 (range) is the dates in the range A2:A.

For this comparison, we use the XMATCH function:

XMATCH(seq,range)

Here, the search_key is the sequence of dates (seq), and the lookup_range is the dates in A2:A.

If a date in the sequence is not found in the range A2:A, the XMATCH function returns a #N/A value. We use the IFNA function to replace #N/A with a blank value:

IFNA(XMATCH(seq,range))

The FILTER formula filters the sequence of dates if the combination of IFNA and XMATCH is equal to blank.

And voila! That’s how you find missing sequential dates in a list in Google Sheets.

Oh, and by the way, the formula works just as well with a list of numbers. Give it a try!

If you need further resources on related topics, you can visit Crawlan.com.

So, go ahead and uncover those missing dates in your Google Sheets. Your besties will be in awe of your newfound skills!

Resources:

  1. [How to Return Start and End Points from a List in Google Sheets]
  2. [How to Fill Missing Dates in Google Sheets (Categorized & General)]

GiGiKing

Related posts