SOLVED

Occasional Contributor

Hi all,

I've been tasked with creating an Excel sheet which displays details of employee annual leave.

We would have a list of leave dates and the aim would be to display the details in a dashboard whereby the appropriate date boxes show whether or not the employee has booked leave.

The following screenshot shows a mockup of the dashboard and the screenshot below that shows the leave dates sheet. As you can see there would be multiple rows per employee on the Leave Dates sheet, with each row being a date range showing the start and end dates of that particular leave entry.

Dashboard:

Leave Dates:

I would be grateful for any tips or suggestions on what formula(s) to use in order to do this.

4 Replies
best response confirmed by Chris House (Occasional Contributor)
Solution

``=SUMPRODUCT((\$A3=\$A\$12:\$A\$19)*(B\$2>=\$B\$12:\$B\$19)*(B\$2<=\$C\$12:\$C\$19))``

Maybe with this rule for conditional formatting.

``=\$B\$3:\$AP\$6``

Conditional formatting is applied to the above range in this example.

Many thanks, this is very helpful.

Please could you tell me what conditional formatting settings you put in place so that it fills in the boxes with colour rather than displaying a 0 or 1?