Forum Discussion
Solved
Annual Leave Spreadsheet
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.
Thanks in advance.
=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$6Conditional formatting is applied to the above range in this example.
=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$6Conditional 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?The rule for conditional formatting is only entered within the manager for conditional formatting but not within the worksheet. In the attached file you can exactly see the settings.
