Easter is around the corner. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and almost forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.
Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.
Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. This is how your data (table name: lvs) looks:
Click here to download the sample file.
You want to answer below three questions:
- How many employees are on leave during Easter holidays (14th of April to 28th of April)?
- How many employees are on approved vacation during Easter holidays?
- How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range ninja
For first question, assume that any employees whose leave is pending will be approved.
Also, assume that Easter season start & end dates are in cells P4 & P5 respectively.
You can use formulas, pivot tables, power pivot measures, VBA or pixie dust to solve the problem. If using pivot table approach, just explain how you would solve in words. For other methods, please post your solution in the comments.
Go ahead and post your questions.
Want some hints..?
What is an Easter themed homework without some clues? So here we go
- Between formula in Excel
- Check if your two ranges of dates overlap
- Range lookup in Excel
- Count unique values subject to conditions
All the best. The weekend forecast is blue skies and light winds. Finally, we will be checking out walking trials in Trelissick park.
