# Date + Duration

edited 06/07/24

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)'

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
edited 01/06/21

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.

• ✭✭✭✭✭✭
edited 01/06/21

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?

• ✭✭✭✭✭✭
edited 01/06/21

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!