using a formula to calculate a date in Gantt schedule

SRaben

I want to use a formula to create the date in my project schedule (not based on predecessor/successor, but based on %complete of a summary task… the date columns dont seem to allow input of formulas? how can I accomplish this:

jmyzk_cloudsmart_jp

Hi @SRaben

If you enable dependency in your project sheet, you can not use a formula in the dates column.

A workaround is to create a separate sheet to calculate data and create a link to the date in the project sheet.

The demo sheet below links another sheet's Start Date for Invoice 50% Fab row.

https://app.smartsheet.com/b/publish?EQBCT=5702f97022ae4e5e850f307145162a27

In the other sheet, % Complete is referenced from the project sheet, and the start date is calculated with the following formula;

[% Complete] =INDEX({project schedule Range % Complete}, MATCH([Dependent Task]@row, {project schedule Range Primary}, 0))

[Start Date] =IF([% Complete]@row > 0.5, TODAY(), "N/A")

Instead of using cell reference like [% Complete]126, I added the [Dependent Task] column to make the formula a column.

https://app.smartsheet.com/b/publish?EQBCT=e373c6b506194d279fdee2188151ba3a

jmyzk_cloudsmart_jp

Hi @SRaben

If you enable dependency in your project sheet, you can not use a formula in the dates column.

A workaround is to create a separate sheet to calculate data and create a link to the date in the project sheet.

The demo sheet below links another sheet's Start Date for Invoice 50% Fab row.

https://app.smartsheet.com/b/publish?EQBCT=5702f97022ae4e5e850f307145162a27

In the other sheet, % Complete is referenced from the project sheet, and the start date is calculated with the following formula;

[% Complete] =INDEX({project schedule Range % Complete}, MATCH([Dependent Task]@row, {project schedule Range Primary}, 0))

[Start Date] =IF([% Complete]@row > 0.5, TODAY(), "N/A")

Instead of using cell reference like [% Complete]126, I added the [Dependent Task] column to make the formula a column.

https://app.smartsheet.com/b/publish?EQBCT=e373c6b506194d279fdee2188151ba3a

SRaben

Thank you!!!

jmyzk_cloudsmart_jp

@SRaben

Happy to help! Your upvote helps others find this. Thanks!😁