substitute plan date with actual date

NeiL C
NeiL C
edited 12/09/19 in Formulas and Functions


Trying to create a formula that can substitute the plan date with actual date.

There are columns of Plan Start/End, Actual Start/End on the sheet, and the actual start & end are initially left blank, and once a task is kicked off, the one assigned is supposed to fill in the actual start, which substitutes the planned start and we can see if the plan end will be pulling or put off. Same principle goes to end date: When a task is finished, that is the actual end date be filled, the plan end date will be substituted and same as the actual end.

Thank you for your help.




  • AverageUser

    I think you're asking for a way to "Auto Populate" the Plan Start Date with today's date when a task is created? Is that correct?

    If so, I believe this is one of the shortfalls of Smartsheet's current design.

    You can use "=TODAY()" in a Date column, and it will put in the current date... but every time the sheet is modified/loaded, it will recalculate to the current date. So it doesn't lock in the first auto populated value and therefore becomes a useless value (basically a "Last Modified" date).

    I would like to see Smartsheet add a function that allows a blank cell to be calculated when certain criteria are met, and lock that value unless certain other criteria are met.

    Hopefully I'm completely wrong and someone else can tell you of a way to do what you're looking for.


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What you are asking for is possible with a third-party tool like Zapier. But alas it is not possible within Smarthsheet itself. I recommend submitting an enhancement request

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!