Forum Discussion

Thingone's avatar
Thingone
Copper Contributor
Feb 03, 2024

Need help trying to find missing days

I am trying to figure out a way to find the data I want. The data I am looking for is absent days. The system I pull my data from goes nicely into an excel sheet. One column has dates. The next has t...
BI & Data Analysis
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 03, 2024

Thingone 

= LET(
    workdays, WORKDAY.INTL(baseDate,SEQUENCE(14),"0000111"),
    missing,  NOT(SUMIFS(hours, date, workdays)),
    FILTER(workdays, missing)
  )

The first line builds a list of Monday-Thursday workdays.  Then SUMIFS is used to collect the total hours worked for each of those days, and NOT causes the zeros to be returned as TRUE.  Finally the intended workdays are filtered, retaining only days flagged as 'missing'.

Resources