Displaying multiple leave periods on one table

[fullysic]

Hi all, I have a bit of a problem I was hoping you could assist with.

I have a data source that contains leave information for all employees, with employees name, start date and end date of leave. I would like to display this information in an easy to read table, with consecutive dates as the column headers, and the employees names as the row headers.

In a perfect world an employee would only take one leave period per year, and this would just be a simple lookup, but where I'm running into problems is that employees will take multiple leave periods in one year, and thus how do I translate these into my table with formulas?

I think I have a solution to do it in VBA, but this is not ideal and would probably take longer, so I'm hoping someone here could point me in the right direction.

Here is a link to a sample workbook I prepared to demonstrate what I am after. It will give you a much better idea of what I am talking about.

http://d01.megashares.com/index.php?d01=6be9d00

Really appreciate the assistance.

fullysic

 

[fullysic]

Solved:

=IF(SUMIFS($B$3:$B$7,$A$3:$A$7,$E3,$B$3:$B$7,"<="&F$2,$C$3:$C$7,">="&F$2)>0,"Leave","")