Calculating multiple future dates based on one completion date

**Staceyd** · 11-04-2010, 12:48 PM

I need help. I need to calculate a series of dates that action items need to be completed, based on a future date. The future date could be a year out, or two weeks out, but the series of completion dates need to be proportional. That's where I am getting stuck.

For instance, if I have a future completion date of 12/31/11

Item A needs to be completed by x date
Item B needs to be completed by y date
Item C needs to be completed by z date

This formula needs to work regardless of how far or near the completion date is, so I am thinking a percentage of time left may be needed? An ideal time line would be over the course of one year. But if the completion date is sooner, the spreadsheet needs to automatically adjust to accommodate the amount of time left. Any thoughts on how I can make this work?

Thanks!

**Palmetto** · 11-04-2010, 01:48 PM

You don't provide enough details to provide a solution. What are the intervals for xyz dates?
Are you attempting to evenly distribute the dates?

You should upload a sample workbook that manually shows a few expected results to give example of the desired outcome and provide a more detailed explanation.

**Staceyd** · 11-04-2010, 02:10 PM

Thank you for your help! Okay, here is my spreadsheet. I have it currently based on a one year time line. But I would like the fields to populate proportionately, no matter how soon or how far out the date is.

For instance, if client a has a target date of one year from now, the cells will populate the due dates based on one year out date. But if client b has a target date of one month from now, the cells will populate with due dates that correspond with her short time line. Is this even possible?

Another possibility would be to put a rule on any populated due date that is past to indicate "Now" or "Today." How could I do this if the other option is not possible?

Thanks so much!

Stacey

**Palmetto** · 11-04-2010, 03:38 PM

You already have a formula that populates dates. How is this formula not working for you and what results do you expect?

It's not clear to me what you mean by "proportional". If the target date is one week from today, what results do you expect to see? You have tasks that range from one year out to the same day as the target date and they occur at dissimilar intervals (months, weeks, days, year).