Finding Missing Dates

[acarey2748]

Hello,

I am new to this forum and enjoy reading responses to learn more about excel. Anywho, I am working on a spreadsheet and trying to locate missing dates to determine absences. The issue I am having is employees clock in and out multiple times a day (for lunch). So, in doing so it will create duplication of dates (see excel example). Not sure how to proceed. Thank you for your help in advance!

[Richard Buttrey]

Hi,

One way would be a helper column which concatenates the date and the employee number, then in a table consisting of every date in a vertical column and each employee number across the column headings, use an =MATCH() formulae to determine whether the combination of date and employee number exists.

See attached.

[MarvinP]

Hi acarey and welcome to the forum,

I have 2 ways to count those missing days. On your June sheet I created a table to the right with all workdays and then did a count for the first few people. You would need to do a different range for each and that seemed too hard, so I copied your data to sheet 1 and inserted a column to give a "Full Name" formula that I copied down to the bottom. THEN a Pivot Table to the right of that data might be exactly what you are looking for. See the attached. Time to learn more about Pivot tables?

Missing Work Days.xlsx

[MarvinP]

Hey Richard,

Those aren't employee numbers. It looks like they are all the same number. Maybe they are employee numbers, before the OP cleaned up the data??

[tim201110]

=IF(ISNA(MATCH($A4,INDEX(June!$C:$C,B$3+1):INDEX(June!$C:$C,C$3),)),"abs","job")

[davesexcel]

IsNa.JPG
Match.JPG

Formula for Joe, then drag down

Please Login or Register  to view this content.

Formula for John, then drag down

Please Login or Register  to view this content.

[acarey2748]

Marvin you are a genius! Didn't even think about using a Pivot table. It makes it easy to see if there are any "missing dates".

[acarey2748]

I will have to try that as well. I haven't used match or index much, more familiar with vlookup. Thank you!