I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.
Re: Calculating # of Overlapping days with several date ranges.
Hello Carl,
Are the times relevant or do you only care about the dates. What result would you expect for that example, if you are counting days that are covered at least once won't it just be the same as NETWORKDAYS between 1st and 30th of June?
Re: Calculating # of Overlapping days with several date ranges.
let's just focus on the dates. I know that the June 1 - June 30 encompasses all the other dates in this case, but the data will change as users input new dates. Therfore, the formula would need to accomodate all possible combinations of dates (assuming that sent out is always greater than received.)
the whole point in doing this is that I will sum up the business days between all of the six previously mentioned dates and subtract the total quantity of overlapping days to know how long an item is in and out of my hands over the length of a proccess.
Re: Calculating # of Overlapping days with several date ranges.
Assuming you have start dates/times in A2:A7 and end dates/times in B2:B7 this formula will count the number of different business days (Monday to Friday).
Re: Calculating # of Overlapping days with several date ranges.
Thanks! the formula works very well. Now I wonder if it's possible to only have some of the cells filled out (eg 4 date ranges instead of all the 6) If I remove a date range I get #REF!. Also, if I were to use time in the calculations, would I only need to change the "WEEKDAY" to NETWORKDAYS().
Once more, thanks for your help, I greatly appreciate!
Re: Calculating # of Overlapping days with several date ranges.
If you use the times how are you expecting that to work? Do you want to count hours only within a specific period or for 24 hours a day during work days?
Re: Calculating # of Overlapping days with several date ranges.
Can someone explain this formula to me? Why would I use MMULT? Also, if I had a different date format i.e. 1/12/10 instead of the example in this thread would I have to use the INT function?
Re: Calculating # of Overlapping days with several date ranges.
Hello tvnsf,
Please can you start your own thread with an explanation of exactly what data you have and what you want to achieve, preferably with a small example - there are probably better ways to achieve what you want rather than using the solution here
Re: Calculating # of Overlapping days with several date ranges.
Hello daddylonglegs,
Your formula to calculate the number of days excluding overlapping days is outstanding. I've used the same formula and it worked great. However, I just need one more thing in addition that is to add a condition.
I've minimum 2 or maximum 11 date ranges for each item and in another sheet i have unique items where i need the same formula but the condition is for each item. Please reply on the same thread as I'm very new member of this forum just registered and doesn't know how to give reference to this post and ceate a new thread. As my question is already answered in this post what i need more is a condition.
LinkBack URL
About LinkBacks
Calculating # of Overlapping days with several date ranges.
Register To Reply
Bookmarks