Populate Dates Between a Start Date and Finish Date
I am trying to create an element of capacity planning for the company i work for, and am having issues with the start date and finish date.
I have a master production planning sheet which has a basic start date for the job, and a finish date for the job. This shows it nicely on the gantt chart and all the days that are spanned as expected.
I then have a capacity estimating sheet, where i have how many hours i have available listed on it for each day of the year.
I basically want a formula to say "if a job is listed on the production planning sheet, mark 8 hours for every day until the finish date for this specific job". I have made the first step through a SUMIF that basically says if the date on the capacity sheet shows on the planning sheet, add the number of hours (as there is often multiple jobs on the same day), but this obviously only works for the start date, and is not spread through the whole period.
sort of wondering if there is a way to do this using SUMIFS by saying if the date is <than the date on the capacity sheet, but >the finish date on the planning sheet then sum it, but not sure if this is correct, nor how it would look exactly..
Not sure if this makes any sense?? Hopefully someone can help me!!
Help Article Resources
Check out the Formula Handbook template!