Recently I came across a very interesting problem, I thought I’ll share it with you all and learn different ways of solving this.
Here is a simple 10 row data with Project Name, Travel Start Date and Travel End Date.
Problem : Your task is to find the number of unique travel days for each project. For instance, the dates for Project 3 (in row 2 and 3) are over lapping. Overlapping dates for a particular project need to be counted once.
Here is how the output should look like
DOWNLOAD THE PROBLEM EXCEL FILE
Take a shot at doing this. Couple of instructions
- Don’t start grappling with this manually. Come up with a technique that is automated.
- You may use Excel, Power Query or even Power Pivot (DAX) for it.
- Personally I am interested to see how can you solve this using Excel or DAX. Solving this using Power Query is a cakewalk.
- I’ll post my solution in a day or two.
My Solution: Posted on 22 Nov 2018
I have solved this using
- Excel’s TEXTJOIN function + a bunch of patchy manual work
- Power Query’s Number.From function
- And Using DAX – The main highlight is using the GENERATE Function
Please post your solutions in the comments, if need be please leave a link to download your file. Cheers
DOWNLOAD THE SOLVED EXCEL FILE FROM BELOW
