Formula to calculate number of days in process

Alane

I am looking to create a formula that will stop calculating once the checked box column is marked. The formula I am using to count the days is =TODAY() - [Date added]@row.

I would like it to stop counting once the box is checked but not revert to blank. As an example if I checked the box I would like it to stay at 97 and stop counting. Any suggestions?

Alane

@Andrée Starå @ray,

Hi following up here. We figured out the formula for this to work. I wanted to share this with both of you. Please see below:

=IF([Moved to Negotiations]@row = 0, TODAY() - [Created date]@row, IF([Moved to Negotiations]@row = 1, [Date moved to negotiations ]@row - [Created date]@row))

Ray Lindstrom

@Alane,

Unfortunately I don't believe that's possible.

The ideal formula would be

=IF([Moved to Negotiated]@row = 0, TODAY() - [Date added]@row,)

The problem is Smartsheet requires, at the very least, some value in the else parameter, like this.

=IF([Moved to Negotiated]@row = 0, TODAY() - [Date added]@row,"")

Notice the "" before the closing bracket. That leads to returning an empty string value which would result in no date being populated once the Moved to Negotiated box is checked.

Smartsheet does not allow an IF statement to not include an Else parameter. I wish it did!

If you point the Else back to the Days on Sheet cell, you'll get a circular reference error. Otherwise this would result in the same as that "ideal formula" above.

Here's another post looking for the same thing, and a 3rd party solution. https://community.smartsheet.com/discussion/2099/still-looking-for-an-if-function-that-does-nothing-to-the-cell-if-false

Be careful though. The recommendation by BobbyLite is not a solution, and will also cause a circular reference error.

All the best,

-Ray

Alane

@ray,

Thank you for your insights. I was looking at this other article where they were able to accomplish something similar using NETWORKDAYS but I believe this would require me to add a second date column/completion column. Nice thing about the way the sheet is set up is that it starts counting when added too. Adding a second date would require users to add this in. Here is the article for reference. Formula to calculate number of day in process — Smartsheet Community

Thank you for helping me troubleshoot.

Ray Lindstrom

@Alane,

Good to know. I'll have to check that out. I do use networkdays in several formulas. It's a great tool!

I logged back on because I was thinking of how to accomplish what you're trying. I thought of using automation to enter a value into a helper column, copying the date that the box gets checked. Problem is the "Change a cell value" feature does not support adding formulas. It will display your formula as a string. If it were possible, that automation with a helper column would be a simple solution.

I created a test sheet for this, and the only thing keeping me from success was the lack of formula support in the Change a cell value feature (helper column worked along with checkbox preventing the count of days from increasing).

Interesting post about that here: https://community.smartsheet.com/discussion/87455/what-is-the-syntax-to-use-automation-to-change-cell-value-to-another-cells-value-same-sheet

So we've learned that you can't:

1.) Use an IF statement without an Else parameter (a "do nothing" Else).

2.) Enter a formula into a cell using automation.

Leaving us tripping over circular redundancies. Doh!

All the best to you, and I hope you find a brilliant solution!

-Ray

Alane

Thank you for going the extra mile. I hope SS sees this and uses it to create enhancements.

Andrée Starå

Hi @Alane

I hope you're well and safe!

To add to Ray's excellent advice/answer.

• You could use a Workflow combined with the Record a Date Action to record the date when the Checkbox is checked and then modify the formula to reference the new date column.
• Another option: Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alane

Hi @Andrée Starå,

I have added a helper column to add the date once the box has been checked. I am not sure how to modify the existing formula that is counting the days on the sheet. As I would like it to count the days on the sheet and then stop counting once checked. Current formula listed below that I am using.

=TODAY() - [Date added]@row

Alane

@Tina,

Adding you to thread so we can learn together the best way to accomplish this.

Tina Rustvold

@Alane By George Ashlee I think we got it! Taking into account Ray's point of needing an 'else', it took time for us to get there, but by nesting an IF formula with your checkbox column we were able to provide that 'else'!

=IF([Moved to Negotiations]@row = 0, TODAY() - [Created date]@row, IF([Moved to Negotiations]@row = 1, [Date moved to negotiations ]@row - [Created date]@row))

By subtracting different dates, we are able to 'freeze' the Number of Days on the sheet.

Alane

@Andrée Starå @ray,

Hi following up here. We figured out the formula for this to work. I wanted to share this with both of you. Please see below:

=IF([Moved to Negotiations]@row = 0, TODAY() - [Created date]@row, IF([Moved to Negotiations]@row = 1, [Date moved to negotiations ]@row - [Created date]@row))

Ray Lindstrom

@Alane @Tina Rustvold,

Nice work! So glad you got it worked out, and thanks for sharing!

Andrée Starå

@Alane

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!