Determining the First Date with No Transactions

Mary has a worksheet that contains transactions from the company accounting system. The worksheet is sorted by column A, which contains the transaction dates. Mary wonders if there is a formula that will let her know the first date in column A for which there is not at least one transaction.

Mary's question is a bit ambiguous, as she doesn't make it clear what constitutes "no transaction" for a date. It could mean that Mary is looking for the first date that is skipped in column A ("no transaction" means no row at all) or the first date for which there is nothing in a corresponding transaction column ("no transaction" means nothing in, say, column B for the date in column A).

Let's look at the first possibility, in which Mary needs to know the first skipped date in column A. I'm going to assume in all of these formulas that the dates are in the range A2:A1000. The following formula looks at the dates and compares them to the dates in the cells offset by one row. If the difference is greater than 1, then that is the date that is returned by the formula:

=INDEX(1 + A2:A999, MATCH(1, --(A3:A1000 > 1 + A2:A999), 0))

The output of this formula, as is the case with all formula results in this tip, needs to be formatted as a date to be meaningful. If you are using Excel 2021 or later, then the following version can be used. The benefit is that it returns "Not Found" if there are no gaps in the dates.

=LET(x, 1 + A2:A999, XLOOKUP(1, --(A3:A1000 > x), x, "Not Found"))

You could also use the following formula if you are using Excel 2024 or later:

=LET(x,A2:A1000,1+@FILTER(DROP(x,-1),DROP(x,1)-DROP(x,-1)>1))

If you use this formula, you may get a warning from Excel suggesting that you move the @ sign to before LET instead of the position shown. The supposed reason for this is for compatibility with older versions of the program. However, FILTER and LET require Excel 2021 or later and DROP requires Excel 2024 or later, thus compatibility with older versions is a moot point.

If Mary wanted to find all the dates missing from column A, then this formula will do the trick nicely:

=LET(Transdates,A2:A1000, Mindate,  MIN(Transdates), Maxdate,  MAX(Transdates), Alldates, SEQUENCE(Maxdate-Mindate+1,,Mindate,1), UNIQUE(VSTACK(Transdates, Alldates),,TRUE))

The formula will spill however many dates are missing from column A between the earliest date and the latest date in the column. Since column A is already in ascending order, the dates produced by the formula will also be in ascending order.

The second possibility for Mary's data is that every date appears at least once in column A, and "no transaction" is determined by a value missing in column B. The need would be to return the first date with a blank value in column B. If this is the case, then the following is probably the easiest formula to return the date:

=INDEX(A2:A1000,MATCH(TRUE,(B2:B1000="")+(B2:B1000=0)>0,))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13984) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.