Date Calculation \ Automated based upon cell value

NTG

Good day Everyone:

I have a field set up with automated current date, that is used to calcuate\count the # of days of task duration based on current date and task start date.

trying to figure out a way to have Actual duration stop counting once the “Sate” of that task.. reflects “Completed”. Or Actual Duration stops calculating if the actual finish date is populated. Doesn’t have to be both one or the other would be satisfactory. 

Along with that if the State of the task is completed, is it possible for the Actual duration cell to recognize that and insert the date that it was flipped to “completed”?

 

Mike Wilday

No that is currently not possible with smartsheets. There have been lots of people wanting a timestamp type function that would set a date when a checkbox is checked, or to access the date a cell was changed (View Cell History) seems to record a cell's last edit date. It would be great to be able to access that date somehow like maybe =LastCellHistory([column 1]23

I recommend submitting a Product Enhancement Request to add your vote. 

NTG

Found a workaround. 

I have a cell set up specifically to populate with today's date and bench the formula from their. This works in tandem with a project status column coupled with the planned start, vs actual start with an actual finish for each task/deliverable

=IF(State1 = "Complete", [Actual Finish]1 - DATEONLY([Actual Start]1), IF(State1 = "In Progress", [Todays Date]$1 - DATEONLY([Planned Start]1)))