Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Date + Duration
In a project sheet, I have the following columns:
[Estimated Start Date], [Actual Start Date], [Estimated End Date], [Actual End Date], and [Duration].
I would like to figure out a formula that will take the Actual Start Date plus the duration to give me the Estimated End Date.
This one is not working...
[Estimated End Date] '=SUM([Actual Start Date]1, [Duration]1)'
Answers
-
Hi @Jonathan Beaty ,
Try:
=[Actual Start Date]@row + [Duration]@row . Should work If your duration is in days.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Still not working for me. I also added a new column titled ‘Days in Duration’, since ‘Duration’ adds a ‘d’ to the number of days. For example, 10d Duration, 10 Days in Duration. Still not working
-
Hi Jonathan,
Are you getting an error or the wrong answer?
The [estimated end date] column needs to be date column.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
A couple ideas to strip out the "d" from you duration and ensure it's a value so you can do math:
[Estimated End Date]@row =[Actual Start Date]@row + IFERROR(VALUE(LEFT([Duration]@row, 3)), IFERROR(VALUE(LEFT([Duration]@row, 2)), VALUE(LEFT(Duration]@row, 1)))))
Or
=[Actual Start Date]@row +VALUE(LEFT[[Duration]@row, (FIND("d", [duration]@row)-1))
If you're using a project sheet and enable dependencies the calculations should be automatic. That could be a problem too because with dependencies activated you can't put formulas into the duration or date columns.
Any luck?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I will try the ideas above and advise. One of my concerns is the statement above stating "The [estimated end date] column needs to be date column." It is a date column, but if I restrict it to dates, will it let me build a formula in that cell?
-
@Jonathan Beaty If it is a date column, it will be able to accept formulas that output a date.
=[Actual Start Date]@row + Duration@row
should be working depending on the Duration column. If it is a built in duration column used in dependency settings, then it should be working. If it is not a dependency column, then you should be able to just enter the number of days without adding the "d" at the end.
What is the issue you are running into? An error, incorrect result, or something else?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
