Creating a formula to populate range of cells between dates based on start and end dates

Hello All,

I hope you can help.

I am trying to create a formula, which populates a range of cell (months) based on a given start and end date AND based on resource utilisation. Below is an example of what I am attempting to achieve as an end result:

Attachment 332596

I wish the populated cells to be formula driven by the (headcount x days available in the month) and only for the months between the date range (start and end date).

I look forward to your help and guidance.

Many thanks in advance,
Remon

Hi,

Not clear could u explain in more detail.

Punnam

Hi Punnam,

Forgive me, I'll try again using an example (Let's use the 'Developer' Profile):

1) The current utilisation of the Developer profile is 1 i.e. 100%. Each month (row 2) there are 21 working days available. Therefore, the Developer will work 100% of the days available, i.e. 21 days.
2) The start and end date date is noted in column D and E
3) On that basis, the cells in row 5 between column G and R are populated. Being, Resource Utilisation x days available each month between start and end dates.

I want the cells between column G and R to be formula driven using the column C (headcount) and start and end dates (column D and E). So, if I were to change the utilisation to say, 0.5 or the start or end dates, the populated cells between G and R also change.

I hope that clarifies what I am looking for.

Many thanks,
Remon

HI,

Check this file.

Punnam

Hi Punnam,

You are a legend - thats' brilliant!

Many thanks
Remon

Hi,

Welcome Thanks for the reps.
As u r question answered make u r thread tools and mark it solved .

Punnam

Hi Punnam,

I've just noticed something! :$

The real data I am applying this formula to is for a date rage of more than 12 months, i.e. 5 years. I notice the formula doesn't work beyond 12 months. As the look up is on "month" and not "month and year". Is there something I can do/you can show to amend this?

Thanks,
Remon