CODEWITHSUNDEEP
google-sheets
Zatin Dharmapuri
Zatin Dharmapuri

Reputation: 49

List all missing Dates between a Start Date and Today()

Sample Sheet - With Intended Results

I need help in listing all missing dates from a Google Sheets column that has a start date up until Today().

Date Range: Attendance!G2:G

Formula in: DashBoard!T1

Below is a partially working formula that displays the first missing date only. But I require it to list all missing dates.

Partially Working Formula:

=ARRAYFORMULA(SMALL(IF(COUNTIFS(Attendance!G2:G, "<="&ROW(INDIRECT(MIN(Attendance!G2:G)&":"&MAX(Today()))), Attendance!G2:G, ">="&ROW(INDIRECT(MIN(Attendance!G2:G)&":"&MAX(Today()))))=0, ROW(INDIRECT(MIN(Attendance!G2:G)&":"&MAX(Today())))), ROWS($A$1:A1)))

Thanks! Zatin

Upvotes: 1

Views: 1111

Answers (2)

ttarchala
ttarchala

Reputation: 4577

=filter(
   arrayformula(Attendance!G2+row(indirect("1:"&today()-Attendance!G2))),
   isna(match(
          arrayformula(Attendance!G2+row(indirect("1:"&today()-Attendance!G2))),
          Attendance!G2:G29,
          0
        ))
 )

Explanation

The main idea is that "list all missing" is formally the result of subtracting the set of existing dates from the set of all possible dates.

To implement the set subtraction (complement) we use a composition of formulas, FILTER + ISNA/MATCH. The first argument to FILTER is the set we are subtracting from, the second argument to MATCH is the subtracted set. The ARRAYFORMULA generates a list of all possible dates between 2 given dates.

The repetition of ARRAYFORMULA is unfortunate, but there is no "let" keyword or equivalent for Google formulas. If anyone knows how to avoid it, I would be grateful for a comment or correction to the answer.

Upvotes: 2

TheMaster
TheMaster

Reputation: 50756

=FILTER(WORKDAY.INTL(Attendance!G2,ROW(A1:INDEX(A:A,today()-Attendance!G2)),"0000000"),ISNA(MATCH(WORKDAY.INTL(Attendance!G2,ROW(A1:INDEX(A:A,today()-Attendance!G2)),"0000000"),Attendance!G2:G,0)))
  • WORKDAY.INTL to list all dates from start date to today
  • MATCH to find all the listed dates above in the given list.
  • FILTER(,ISNA()) to find missing dates

Upvotes: 2

Related Questions