Partners,,,, russianxnxx

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




Take a shot at doing this. Couple of instructions

  1. Don’t start grappling with this manually. Come up with a technique that is automated.
  2. You may use Excel, Power Query or even Power Pivot (DAX) for it.
  3. Personally I am interested to see how can you solve this using Excel or DAX. Solving this using Power Query is a cakewalk.
  4. 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


    ⬇️ Pop in your Name & Email to get the file!



    Topics that I write about...