If NOT complete, change end date to TODAY()

jkfig

Hi

 

I'm trying to automatically increase the finish dates of tasks not completed, that were due for completion before today, to today. Is there any way to do that?

Any help would be really useful

Shaine Greenwood

Hi,

I edited your tags for better parity with your post.

One way you can accomplish this is by putting an IF statement in your finish date cells. The construction of your IF statement will depend on how your sheet is structure.

Note that if you have the dependencies functionality enabled, formulas can't be placed in start or finish date columns.

Details on creating formulas in Smartsheet can be found in our help article: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

More on the IF function itself: https://help.smartsheet.com/function/if

jkfig

Thank you Shaine.

Unfortunately that somewhat defeats the object if dependencies can't be enabled. 

To be clear what the goal was / is; at the start of the project I'd enter the forecast duration in a column as usual, I would also enter a budgeted duration (more often that not this would be the same). If the task hadn't been marked complete and the forecast finish date was less than today then the finish date would become today and the related dependencies would update. This was I could maintain an accurate forecast without having to do this manually.* 

I'd put in place conditional formatting to flag if that would result in the task going over budget.

The way I'd previously done it in spreadsheets managed in excellent was to run a macro.

*When the project is only 10's of tasks this isn't so much of an issue, it is more of an issue when it is more complex and involving a large number of resources. In practical terms this can save an hour of monotonous admin a couple of times a week.