Staff Holiday Planner

[ABSTRAKTUS]

Hi all,

I have tried a few different approaches, but no luck. I am trying to build a staff holiday planner, that would then return all the dates between start and end of their holidays.

Assume first couple of columns are for names and surnames, the following 365 columns represent every day of the year. This is the sheet I want to get results in (call it RESULTS). The database for staff holiday is on another sheet: again 2 columns for names and surnames, 3rd column for start dates and 4th column for end dates.

Question: how do I plot all staff holidays in those 365 columns? Logic would look like this: in the row find JOHN SMITH on a database and return "H" between his start and end of holidays, go searching in the next row until the end of the list.

I'm not the best at describing what am I trying to achieve, so attached a dummy with desired results.

Guys, any help I will much appreciate!

[tim201110]

=IF(SUMPRODUCT(--($B3=_t[NAME]),--($C3=_t[SURNAME]),--(D$2>=_t[FROM]),--(D$2<=_t[TO]))=0,"","H")
_t is a name for your data

[Pete_UK]

How many employees are you likely to have?

Pete

[ABSTRAKTUS]

500 max...

[ABSTRAKTUS]

Hi Tim,

When I was replying yesterday the site has crashed. Your formula works fine on a dummy, but when I replicated everything on the actual workbook it throws an error on _t[NAME]... I have named the ranges as _t, but no luck.

[ABSTRAKTUS]

Bump........

[Pete_UK]

In the attached file I have used this formula in cell D3 of the RESULTS sheet:

=IF(COUNTIFS(DATA!$A:$A,$B3,DATA!$B:$B,$C3,DATA!$C:$C,"<="&D$2,DATA!$D:$D,">="&D$2),"H","")

and copied across and down to give you the desired results.

I have also changed the way you generate the dates in row 2, so that you can easily change the year in cell A1 and the dates will automatically follow.

Hope this helps.

Pete