30 Weeks Out - Formula Question
Hello,
I have a project that has certain tasks that need to start a specific number of weeks ahead of the project end date. For example, I have 30 weeks out tasks, 29 weeks, out 20 weeks out, etc.
On my project sheet, the END DATE is December 1, 2021. I need a formula that would tell me what the START DATE is for exactly 30 weeks out meaning figure the date in reverse - what is the date 30 weeks behind December 1, 2021? It should be something like April or May.... just can't seem to work the forumula.
hope that makes sense!
Thanks so much!
Lynda
Best Answer
-
It sounds like you are using dependencies. Formulas can't be used in columns that are referenced by the dependencies in the project settings.
Answers
-
Hi Lynda,
As long as your END DATE and your START DATE columns are formatted as date columns, you can do the following to calculate 30 weeks prior to the end date:
=[end date]@row - 210
If you want to avoid doing the math, you could set it up as
=[end date]@row - (7*30)
OR if you have a duration (in weeks) column already in your sheet, you could have it automatically calculate:
=[end date]@row - (7*[duration in weeks]@row)
Hope this helps!
Best,
Heather
-
Thanks, Heather. This did not work in my sheet. All it did was paste in the formula but no action was taken. So, now I literally see the formula in the Start Date column. ???
-
It sounds like you are using dependencies. Formulas can't be used in columns that are referenced by the dependencies in the project settings.
-
Thank you! Your solution got me squared away. I did have to change the formula slightly to:
=([Due Date]@row - 210)
I appreciate your help!
Lynda
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!