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 das...
=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?
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.
Many thanks. That's just the job.
I'd been trying to put something together for a few days and the SUMPRODUCT function seemed to be the best option, but I've been struggling to understand how the SUMPRODUCT function works, especially when it comes to date ranges.