SOLVED

Annual Leave Spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3533939%22%20slang%3D%22en-US%22%3EAnnual%20Leave%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3533939%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20tasked%20with%20creating%20an%20Excel%20sheet%20which%20displays%20details%20of%20employee%20annual%20leave.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20would%20have%20a%20list%20of%20leave%20dates%20and%20the%20aim%20would%20be%20to%20display%20the%20details%20in%20a%20dashboard%20whereby%20the%20appropriate%20date%20boxes%20show%20whether%20or%20not%20the%20employee%20has%20booked%20leave.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20screenshot%20shows%20a%20mockup%20of%20the%20dashboard%20and%20the%20screenshot%20below%20that%20shows%20the%20leave%20dates%20sheet.%20As%20you%20can%20see%20there%20would%20be%20multiple%20rows%20per%20employee%20on%20the%20Leave%20Dates%20sheet%2C%20with%20each%20row%20being%20a%20date%20range%20showing%20the%20start%20and%20end%20dates%20of%20that%20particular%20leave%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDashboard%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22dashboard.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382387i8552196F87EDECFA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22dashboard.PNG%22%20alt%3D%22dashboard.PNG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ELeave%20Dates%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22leave%20dates.PNG%22%20style%3D%22width%3A%20351px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382389iAC6677F34A971101%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22leave%20dates.PNG%22%20alt%3D%22leave%20dates.PNG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20grateful%20for%20any%20tips%20or%20suggestions%20on%20what%20formula(s)%20to%20use%20in%20order%20to%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3533939%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3534195%22%20slang%3D%22en-US%22%3ERe%3A%20Annual%20Leave%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3534195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F148281%22%20target%3D%22_blank%22%3E%40Chris%20House%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((%24A3%3D%24A%2412%3A%24A%2419)*(B%242%26gt%3B%3D%24B%2412%3A%24B%2419)*(B%242%26lt%3B%3D%24C%2412%3A%24C%2419))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20rule%20for%20conditional%20formatting.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%24B%243%3A%24AP%246%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EConditional%20formatting%20is%20applied%20to%20the%20above%20range%20in%20this%20example.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22conditional%20formatting.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382401i6E5E868793D4A2AE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22conditional%20formatting.JPG%22%20alt%3D%22conditional%20formatting.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537864%22%20slang%3D%22en-US%22%3ERe%3A%20Annual%20Leave%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537864%22%20slang%3D%22en-US%22%3EMany%20thanks%2C%20this%20is%20very%20helpful.%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20could%20you%20tell%20me%20what%20conditional%20formatting%20settings%20you%20put%20in%20place%20so%20that%20it%20fills%20in%20the%20boxes%20with%20colour%20rather%20than%20displaying%20a%200%20or%201%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3538021%22%20slang%3D%22en-US%22%3ERe%3A%20Annual%20Leave%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3538021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F148281%22%20target%3D%22_blank%22%3E%40Chris%20House%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20rule%20for%20conditional%20formatting%20is%20only%20entered%20within%20the%20manager%20for%20conditional%20formatting%20but%20not%20within%20the%20worksheet.%20In%20the%20attached%20file%20you%20can%20exactly%20see%20the%20settings.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3538527%22%20slang%3D%22en-US%22%3ERe%3A%20Annual%20Leave%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3538527%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks.%20That's%20just%20the%20job.%3C%2FP%3E%3CP%3EI'd%20been%20trying%20to%20put%20something%20together%20for%20a%20few%20days%20and%20the%20SUMPRODUCT%20function%20seemed%20to%20be%20the%20best%20option%2C%20but%20I've%20been%20struggling%20to%20understand%20how%20the%20SUMPRODUCT%20function%20works%2C%20especially%20when%20it%20comes%20to%20date%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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:

dashboard.PNG

 

Leave Dates:

leave dates.PNG

 

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

 

Thanks in advance.

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

@Chris House 

=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.

conditional formatting.JPG 

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?

@Chris House 

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.