Skip to main content

I'm hoping someone can help me with a date formula and how to implement it.

I've got a project plan (with dependencies) and I've got three fields, "start date" "end date" and duration.

  • The project has a kick-off date and all start and end dates cascade from there based either on the duration of the task or a a dependency.
  • All dates to be calculated on a M-F work week.
  • When a task is marked complete, the end date needs to be updated to the current date (thus re-calculating the tasks dependent on that end date).
  • Bonus points for the ability to integrate holidays into the calculation.

I'm completely lost on how to achieve this. Any help you can offer is much appreciated.

Thanks.

I think you will want to add a lookup of the end date of the blocking task. Then on the start date add a formula to calculate that. You can then setup an automation to update records linked to another record when that record is marked as completed, having the same automation update the end date. You can use the workday formula to bring in the calculation and use the days needed to calculate the start and end days. 

Thanks for the reply, @joshsorenson 

I have the following formula that seems to be working to calculate the date from the start date + duration

IF({Start},DATETIME_FORMAT(DATEADD(Start, {Duration In Days}, "days"),'MM/DD/YYYY'),"")

 I'm not updating on completion date (based on checking off that the task is complete) yet but that seems pretty doable.

The challenge I'm having right now is that using that formula above, I cannot make that field my end date in the Gantt chart. It says the field isn't a date. I would have thought the DATETIME_FORMAT would have done that.

Thoughts?

Thanks for the reply, @joshsorenson 

I have the following formula that seems to be working to calculate the date from the start date + duration

IF({Start},DATETIME_FORMAT(DATEADD(Start, {Duration In Days}, "days"),'MM/DD/YYYY'),"")

 I'm not updating on completion date (based on checking off that the task is complete) yet but that seems pretty doable.

The challenge I'm having right now is that using that formula above, I cannot make that field my end date in the Gantt chart. It says the field isn't a date. I would have thought the DATETIME_FORMAT would have done that.

Thoughts?


Figured this one out by using DATETIME_PARSE

Reply


Terms & Conditions