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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/06/21



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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


    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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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?