Forum Discussion
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.
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.
=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.