Sum Formula to exclude days that overlap in range?

zpeedd3mon

New Member
Joined
Dec 29, 2017
Messages
2
Good Afternoon Community! I have been helped countless times thru all of the posts on this forum (THANK YOU!) but I'm truly stuck this time. I don't have any experience with VBA so I'm trying to accomplish this all using formulas. Here's the problem:


Problem:
I'm trying to set up a Time in Motion study worksheet to see all of the resource needs for our operation over the course of a year. The operation is a farm and there are pieces of equipment that are used in several different areas with different but sometimes overlapping timeframes. My solution is to take the time frame for each task, count the days in between the start and finish dates excluding Sunday, sum the total days (window of opportunity or WOO) and divide by the capacity of one machine to give me the total number of machines needed to accomplish the task within the given WOO. The problem is that there are some tasks that the WOO overlaps and if I do a simple SUM then the WOO may seem much larger than it truly is. For example, if I have 2 different tasks with 150 days in the WOO then it would show 300 WOO and my equipment needs will be 1/2 of what I actually need.


Setup:
I have a master "Equipment" list that holds the equipment specs, capacity, and a timeline of use that comes from: about 6 other sheets specific to a crop. Each of those crop sheets contains an activity timeline with a start and finish date (WOO) and each activity is tied to a piece of equipment from the "Equipment List." The timeline on the master "Equipment" list is populated by the total days of work from the 6 crop sheets. I need a SUM formula that excludes any days that overlap for all of the crop sheets.

Here's the formula I have now:

=IFERROR((SUM(SUMIFS(potatotasks[Acres/Day To Finish],potatotasks[Impliment],$A41,potatotasks[Start Date],"<="&Z$2,potatotasks[End Date],">="&Z$2),SUMIFS(beetstasks[Acres/Day To Finish],beetstasks[Impliment],$A41,beetstasks[Start Date],"<="&Z$2,beetstasks[End Date],">="&Z$2)))/[@[Acre Capacity of Total Impliments]],0)


Screen Shots:
Equipment Master List - http://grant4dfarms.com/publicforums/equipmentmaster.jpg
Crop Task Template - http://grant4dfarms.com/publicforums/croptasktemplate.jpg


Thanks for any help that you can give me!

-Taylor

 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

zpeedd3mon

New Member
Joined
Dec 29, 2017
Messages
2
Still working on this and have found a solution that counts the number of overlapping days if the criteria matches "DMI" but now I'm not sure how to expand this formula to not only look at the table "potatotasks" to looking at 2 tables, "potatotasks" and "beetstasks". Does anyone have any thoughts? Here's where I'm at now:

=SUM(IF(FREQUENCY(IF(potatotasks[Impliment]=F36,IF((MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1<=potatotasks[End Date])+(MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1>=potatotasks[Start Date])>1,MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1,"")),MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1)>1,1,0))

Tables set up like below:

potatotasks table
Implement Start Date End Date
DMI 1/1 1/10
Plow 1/5 1/20
DMI 1/5 1/20

Beetstasks table2
Implement Start Date End Date
DMI 1/5 1/20
Plow 1/5 1/20
DMI 1/15 1/25
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top