Google Sheets技术需求：列出起始日期至今日的所有缺失日期

To get all missing dates from the earliest entry in Attendance!G2:G up to TODAY() and display them in DashBoard!T1 (spilling down the column automatically), use this optimized formula that prioritizes readability and reliability:

=ARRAYFORMULA(LET(
    start_date, MINIFS(Attendance!G2:G, Attendance!G2:G, "<>"),
    date_range, SEQUENCE(TODAY() - start_date + 1, 1, start_date),
    missing_dates, FILTER(date_range, ISNA(MATCH(date_range, Attendance!G2:G, 0))),
    IFERROR(missing_dates, "No missing dates")
))

How It Works #

Let’s break down each part of the formula:

• start_date: Uses MINIFS to fetch the earliest non-blank date from your Attendance column (ignores empty cells that could skew the starting point).
• date_range: Generates a complete sequence of dates from start_date to TODAY()—this creates the full set of dates we expect to see.
• missing_dates: Filters the full date range to retain only dates that don’t exist in Attendance!G2:G (the ISNA(MATCH(...)) check identifies dates not present in the attendance list).
• IFERROR: Handles edge cases where there are no missing dates, returning a clear message instead of an error.

Expected Result Sample #

Suppose:

• Attendance!G2:G contains dates: 2024-01-01, 2024-01-02, 2024-01-04, 2024-01-05
• TODAY() is 2024-01-06

The output in DashBoard!T1 and the cells below will be:

If all dates from the start date to today are present in Attendance!G2:G, DashBoard!T1 will show:
No missing dates

内容的提问来源于stack exchange，提问作者Zatin Dharmapuri