Can you calculate vacation days in a period? [Homework]
Its Friday, that means time for another Excel challenge for you.
This is based on yesterday’s employee vacation dashboard.
Calculate vacation days in a period:
Your mission, if you choose to accept it,
Step 1: Download this workbook.
Step 2: Calculate number of vacations taken in a period. Specifically,
- How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
- How many vacations are taken such that at least one day of vacation is between start & end dates?
- How many people took vacations? (if same person took multiple vacations, then count it as 1)
You are free to,
- Use helper columns
- Any formula
- Play Mission Impossible music in background
- Drink no more than 2 cups of coffee
How to post your answers?
Simple, just post them in comments. Include explanation of your logic too if possible. Click here to post your answers.
Need some clues?
We have them. See these links:
Added later: Solution for this problem
here were quite a few interesting solutions in the comments.
Today, let me explain how to solve these problems using Excel formulas.
Watch below video:
Download solution workbook
|Designing a dashboard to track Employee vacations [case study]||Introduction to DAX Formulas & Measures for Power Pivot|