Populate Dates Between a Start Date and Finish Date

Grant Davies · 05/24/18

Hi,

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!!

Chris McKay · 05/24/18

Hi Grant,

Do you have an image you can share. I'm still unsure about the data contained in each sheet.

I am picturing a Planning Sheet, Capacity Sheet but I'd confused about what the following means:

if the date is <than the date on the capacity sheet, but >the finish date on the planning sheet

What are the 2 dates you are referring to and where is the data coming from?

Kind regards,

Chris McKay

Andrew Stewart · 05/25/18

If I have understood correctly, you have a sheet with at least three columns, and I suggest a fourth, the effort per day, in case some of the jobs get more effort:

Project Start Finish HoursPerDay

Job 1 3/5/2018 5/5/2018 8

Job 2 4/5/2018 6/5/2018 16

etc

For capacity planning you have another sheet with an entry for each day, and you want to know what the total required by all the jobs active on that day is, so you can compare it to the capacity available. Assuming that you create external references for each column in the first sheet, {Start}, {Finish} and {HoursPerDay}, the formula you need for any given date x is

=SUMIFS( {HoursPerDay},{Start},<= x,{Finish},>=x)

It is a little easier if your capacity sheet is organised vertically, eg:

Date Hours Needed

01/05/2018 =SUMIFS( {HoursPerDay},{Start},<= [Date]@row,{Finish},>=[Date]@row)

=[Date]1+1 =SUMIFS( {HoursPerDay},{Start},<= [Date]@row,{Finish},>=[Date]@row)

then you can just drag the second row down to fill the sheet until you reach your desired end date.

You can also do it horizontally, but it will be tricky because you can't mix dates and numbers in the same column.

I hope this makes sense, and helps.

Note: My date formats are dd/mm/yyyy, by the way, you can use whatever matches your locale. Note the Primary column cannot be a date type, you will need at least one column before the date column, it can be blank, but must be of type text.

Andrew Stewart · 05/25/18

You might replace the formula =[Date]1+1 with

=WORKDAY([Date]1,1,[holidays])

if you are not working seven days a week :-)

Populate Dates Between a Start Date and Finish Date

Comments

Help Article Resources

Categories