Date + Duration

Jonathan Beaty

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

Mark Cronk

Hi @Jonathan Beaty ,

Try:

=[Actual Start Date]@row + [Duration]@row . Should work If your duration is in days.

Mark

Jonathan Beaty

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

Mark Cronk

Hi Jonathan,

Are you getting an error or the wrong answer?

The [estimated end date] column needs to be date column.

Mark

Mark Cronk

Mark Cronk

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

Jonathan Beaty

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?

Paul Newcome

@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?