Need help trying to find missing days

Copper Contributor

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




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.

find missing day.png




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

filter mkissing dates.png


@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. 




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),


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.

absence days.png


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