Populate Dates Between a Start Date and Finish Date

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

Find more posts tagged with

Formulas

Gantt View

Cell linking

Comments

Chris McKay

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

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

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

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…