Unique days in multiple date ranges

[phantastic79]

I've been struggling with this problem for a while and cant find the equation.

How would you calculate the number of unique days in a set of date ranges?

Example:

1/1/14 - 1/5/14
1/4/14 - 1/7/14
1/10/14 - 1/12/14

The answer is 10.

But what is the formula that would calculate this and could be applied to any set of date ranges?

Thanks for looking at my issue.

 

[phantastic79]

incorrect?

I thought it was correct....

Unique days in range:

1/1 - 1/5 = 5days
1/4 - 1/7 = 4 days (2 days overlap) - only 2 days unique
1/10 - 1/12 = 3 days

= 5+2+3= 10 .... is that not correct?

Apologies if I'm missing something... or if I stated problem wrong.

I can do the math manually. ..but looking for a formula that calculates unique days in any set of date ranges.

 

[Ishuda]

As Denis said, no magic formula but there is a method. To Denis' method I would add that you would have to go through the dates and put them is order of initial date (or, if you wish, by ending date) of the range of dates for a particular set. The example you give already has the set in order by the initial date, i.e. 1/1, 1/4, and 1/10. Next, go through the list again comparing the ending date of a particular set to the beginning date of the next set. Adjust the beginning date of that next set so there is no overlap. Note that you may totally delete an entry. if, for example if you originally had
1/1 - 1/5,
1/2 - 1/4,
1/4 - 1/7,
1/10 - 1/12,
that process would first get rid of the 1/2 - 1/4 entry to leave your initial example, then change your example to 1/1 - 1/5,
1/6 - 1/7,
1/10 - 1/12
The process could be extended to ranges over years.

This process would probably be easier if the dates were converted to Julian day numbers initially. Note that Julian day number for Jan 1, 2014 is Julian day 2456659. There are free programs which will do the conversion and most spread sheet programs will do the conversion for you with a Julian day function.

 

[phantastic79]

Thanks for the reply

Thanks for everyone's reply.

I was really hoping to find a magic formula for this solution.

I am currently using SQL code to count the number of unique...but I feel it is overly complex.

I was considering populating all the dates values into an array and counting the distinct items but I assumed(didn't actually test it) this method would require more overhead on my server.

I am not very good at math but I once heard that everything in life can be represented with equations. Perhaps that is not true?

Seems like the SQL method or the array method is basically making a computer do manual long division for you.

How would Newton have solved this problem back in the day?

I wonder....

 

[Deleted member 4993]

Thanks for everyone's reply.

I was really hoping to find a magic formula for this solution.

I am currently using SQL code to count the number of unique...but I feel it is overly complex.

I was considering populating all the dates values into an array and counting the distinct items but I assumed(didn't actually test it) this method would require more overhead on my server.

I am not very good at math but I once heard that everything in life can be represented with equations. Perhaps that is not true?

Seems like the SQL method or the array method is basically making a computer do manual long division for you.

How would Newton have solved this problem back in the day?

I wonder....

There is an algorithm which converts every date to a unique number. MS_Excel uses that.

If you write a small program, not an one-liner equation, I believe this task can be accomplished. Befriend a computer science student - s/he can write you one in your choice of language.