Attendance tracker filter issues

sabi.kiss

Board Regular
Joined
Nov 7, 2009
Messages
118
Hi there

Has been a while since I posted here, and most probably someone else asked the very same question, however could not find exactly what I was looking for. Hope everyone is safe during these crazy times.

I have been building an attendance tracker for my employees (200+ employees to be exact) and my monthly dashboard looks quite busy and not the way I would like it to look.
Instead of having 200+ employees showing on my dashboard, and most of them having no leave taken whatsoever within the particular month I have chosen, I would like to filter down and only show those employees who have any leave booked in within the chosen month.

So, here we go, I have an input table with 3 columns, column B being my names field, column C leave start date and column D leave end date.
My filter filed looks like this. Cell K would be the month chosen, then as a helper cell K first day of the chosen month and cell M last day of chosen month.

I have used the below formula to filter through the names column, nd it works, however if I have one employee whose leave is peeking through another month, that filed won't be shown within my result list (result column is K).
INDEX($B$3:$B$10, SMALL(IF($C$3:$C$10>=$L$3, IF($C$3:$C$10<=$M$3, ROW($C$3:$C$10)-ROW($C$3)+1)), ROWS(K$6:K6)))

Example:
1610495236282.png


1610495260357.png


As you can see in the second image, my name isn't picked up within FEBURARY.

Any help would be appreciated.

Many thanks in advance.

Sabi
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
unique.xlsx
ABCDEFGHIJK
1NameStartEndYear2020
2Boon1/25/20201/25/2020MonthJanuary
3Taylor1/14/20201/22/2020
4Tarata1/18/20201/18/2020Boon
5Kiss1/27/20202/5/2020Taylor
6Venour2/2/20202/5/2020Tarata
7 
8 
9 
10 
Sheet2
Cell Formulas
RangeFormula
K4:K10K4=IFERROR(INDEX($B$2:$B$6, AGGREGATE(15,6,(1/((PROPER(TEXT($C$2:$C$6,"MMMM"))=$K$2)*(PROPER(TEXT($D$2:$D$6,"MMMM"))=$K$2)))*ROW($C$2:$C$6)-1,ROWS($K$4:K4))),"")
 

sabi.kiss

Board Regular
Joined
Nov 7, 2009
Messages
118
Thanks for your reply lrobbo314, however one name hasn't been picked up within the JANUARY filter, even though "KISS" does fall within the month of January with a could of days.
Hope you understand what I'm trying to achieve here. So, "KISS" would have to come up in both January & February listings, as its creeping into both months.
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Try this!
{=INDEX($B$2:$B$6,SMALL(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,ROW($B$2:$B$6)-ROW($B$1)),ROWS($K$4:K4)))}
 

Attachments

  • Screenshot (20).png
    Screenshot (20).png
    27.2 KB · Views: 6
Solution

sabi.kiss

Board Regular
Joined
Nov 7, 2009
Messages
118

ADVERTISEMENT

Thanks heaps alz .. works a charm
 

sabi.kiss

Board Regular
Joined
Nov 7, 2009
Messages
118

ADVERTISEMENT

Now is there a possibility on removing duplicate names? At the moment if I have separate leave dates booked for the same person, it will pick the name up twice.
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Hi Try this in K4!

{=IFERROR(INDEX($B$2:$B$6,
SMALL(IF(FREQUENCY(IF(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6)<>FALSE,
MATCH(IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6),IF((--(TEXT($C$2:$C$6,"yyyymmmm")=$K$1&$K$2)+(--(TEXT($D$2:$D$6,"yyyymmmm")=$K$1&$K$2)))>0,$B$2:$B$6),0)),
ROW($B$2:$B$6)-ROW($B$2)+1),ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($K$4:K4))),"")}
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top