Date Calculation \ Automated based upon cell value

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”?

Find more posts tagged with

Sheets

Formulas

IT & Operations

Gantt View

Project Management

Comments

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)))

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…