Use Index(Match(0,If To Get List Of Employees Within Dates For Calendar

Good Morning,

I am trying to create a calendar that shows a list of employees who have vacation time for each calendar date. This has become tricky because the employee vacation start and end dates can be before or after the calendar dates. Formula below, which only captures employees with the current date and the next day's date for vacation. If the employee's vacation extends beyond this time, they aren't listed. The number of employee names should total the number of requests taken by DISNEY and HANNA BARBERA. As an example, Wed, 1/1/2014 should display 7 names, but we only get 4.TEMPScheduling.xlsx

Please Login or Register  to view this content.

Any assistance is appreciated!

Try something like:

=IFERROR(INDEX(VacationLog!$A$1:$A$90,1/(1/LARGE(ROW($I$3:$I$90)*(VacationLog!$I$3:$I$90<=DATEVALUE(F$5&" "&$C$3&" "&$G$2))*(VacationLog!$J$3:$J$90>=DATEVALUE(F$5&" "&$C$3&" "&$G$2)),ROW(A1)))),"")

in F6 as an array formula and copy down. You will need to adjust the month/year cells as necessary to get the right datevalues.