# Need help trying to find missing days

Copper Contributor

# 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 the day of the week. The next has clock times. If an individual doesn’t clock in for a day there is just an absence of record. I thought about searching for consecutive days but the problem is some records are Monday through Thursday. Some Tuesday through Friday. The next kink in the formula is that there could be three Monday records Or 5, or more, for the same date.

how would I look for a missing day? It would be nice if I could tie the search to a date range.

yes this is attendance. Currently for my job the only way to track is to either scroll through pages and find them manually or keep a written record. The information is here. I just have to figure out how to pull it out so it’s easier for me. Thank you for any help in advance.

5 Replies

# Re: Need help trying to find missing days

=IF(AND(\$A4>=\$D\$2,\$A4<=\$E\$2,\$C4=""),A4,"")

For the sample data you can apply this formula which is in cell E4 and filled across range E4:F31. The date range can be specified in cells E2 and F2.

=FILTER(A4:B29,(A4:A29>=E2)*(A4:A29<=F2)*ISBLANK(C4:C29))

If you have access to the FILTER function you can return the results in a spilled range.

# Re: Need help trying to find missing days

@OliverScheurich Thank you for responding. I should have been more clear. I will try to add a picture. There literally is just no record when someone doesn’t clock in. We never work Saturdays or Sundays so those I need to discard and would like to sort by their chosen schedule.  I.e. Monday thru Thursday or Tuesday through Friday. I have a tab for each person that will resemble the following picture. I cut out the lines with data I can’t share.
Columns are as follows.
date,day,clockin,clock out,hours worked.

# Re: Need help trying to find missing days

=LET(uniquedays,UNIQUE(A2:A21),

starttoenddate,SEQUENCE(MAX(uniquedays)+ (3-WEEKDAY(MAX(uniquedays),2) +1)-(INT((MIN(uniquedays)-1)/7)*7+2)+1,,INT((MIN(uniquedays)-1)/7)*7+2),

weekdays,WEEKDAY(starttoenddate,2),
days_mon_to_thurs,FILTER(starttoenddate,weekdays<=4),

compare,XMATCH(days_mon_to_thurs,uniquedays),
IFERROR(FILTER(days_mon_to_thurs,ISERROR(compare)),"no absence days"))

If you work with a recent version of Excel you can apply this formula for a Monday to Thursday schedule. The result is in a spilled range and can be filtered in order to return only the absence days of a specified period of time.

# Re: Need help trying to find missing days

``````= 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'.