How to I make this column stop counting one status is marked complete

ops

=[Today date DO NOT TOUCH]@row - [Date Submitted]@row

the formula i am using for days to resolution column. I would need this column to stop counting once status is marked complete by dropdown. Would need to be a formula I can make into a column formula

VBAGuru

You would need to have a completed date. This could be manually entered or done through an automated workflow that is triggered by marking the task complete. Then you could use the formula

=If(Status@row="Complete",[Completed Date]@row-[Date Submitted]@row,[Today date DO NOT TOUCH]@row - [Date Submitted]@row)

ops

https://community.smartsheet.com/discussion/comment/480132#Comment_480132

The date resolved would be the completed date. I've added a Complete column for them to mark off complete instead of status. Would the new formula be

=If(Complete@row="Complete",[Date Resolved]@row-[Date Submitted]@row,[Today]@row - [Date Submitted]@row)

VBAGuru

That works too!

ops

I've added that formula and it is not working.

VBAGuru

Are your column names still the same as above? The pic above has the column name as Status instead of Complete. Then also, are all the date columns formatted as Date?

But I might suggest one edit that eliminates the need for a Today's date column. I would use the Today() formula in place of referencing a column formula:

=If(Complete@row="Complete",[Date Resolved]@row-[Date Submitted]@row,Today() - [Date Submitted]@row)

ops

Updated columns

VBAGuru

I think yours is working correctly! I would get zero as well for something submitted today.

ops

That's with the old formula. When I type the new one, it says "unparsable"

VBAGuru

I get this

Using

=IF(Status@row = "Completed", [Date Resolved]@row - [Date Submitted]@row, TODAY() - [Date Submitted]@row)

ops

With that new formula, still showing as it doesn't work

VBAGuru

Unparsable is usually a comma or ) out of place in my experience. Can you share your formula?

ops

=IF(Complete@row = "Completed", [Date Resolved]@row - [Date Submitted]@row, TODAY() - [Date Submitted]@row)

VBAGuru

Everything looks correct, maybe the quotes on Completed, but could just be my screen. The only other thing I can think of is one of the @row references is not a color other than black meaning it isn't referencing correctly. To fix, just delete it out and re-reference.

VBAGuru

ops

Do you mind typing that out here so I can copy exactly? :)