Stop a Days Counter when project completion date is entered?

JOHN HONDARACER

I have a days counter formula '=TODAY() - [Execution Completed (Form D)]1' but would like this count to stop once a date is entered into a separate column 'Date MOC Closed (Form E)'?

Andrée Starå

Hi @JOHN HONDARACER

I hope you're well and safe!

Try something like this.

=
IF([Date MOC Closed (Form E)]@row <> "", TODAY() - [Date MOC Closed (Form E)]@row, 
TODAY() - [Execution Completed (Form D]@row)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JOHN HONDARACER

thanks Andree - i can't get this to work was wondering if you can confirm the steps, as follows:

IF([Date MOC Closed (Form E)]@row <> "" - this confirms if there is anything entered into this column the count stops?

TODAY() - [Date MOC Closed (Form E)]@row - this is a count number of days after the job is closed?

TODAY() - [Execution Completed (Form D]@row) - this is my original/existing count that i want stopped if a date is entered in column 'MOC Closed (Form E)

Andrée Starå

@JOHN HONDARACER

Happy to help!

Yes, that's correct!

What happens?

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

JOHN HONDARACER

#UNPARSEABLE unfortunately - i'm not sure why i would need the third section? Was thinking this could be some kind of COUNTIF formula?

Andrée Starå

@JOHN HONDARACER

You need the third section to count the days until the Closed date is added, and then it will be the second section that triggers.

Make sense?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Zvarga

I have a spreadsheet of commitments where I track days on list using the today function. How do I get the count to stop when I enter the word ‘closed’ in my status column? Is there a formula?

Zvarga

Waiting on my question to be answered?

brianswilson

Don't know if this might work better for you, but it is essentially the same as Andrée Starå posted. Obviously you will need to modify this with the proper column names.

= IF(NOT(ISBLANK([Date MOC Closed (Form E)]@row)), TODAY() - [Date MOC Closed (Form E)]@row, TODAY() - [Execution Completed (Form D)]@row)

Zvarga:

I would suggest posting this a a question on its own. While it is related to this questions, it is off topic.

Paul Newcome

I personally would use this for @JOHN HONDARACER's question:

=IF([Date MOC Closed (Form E)]@row <> "", [Date MOC Closed (Form E)]@row, TODAY()) - [Execution Completed (Form D)]@row

Using the IF to output either the Date MOC Closed (Form E) or TODAY() means that it will in fact stop counting once the date is entered. If you are subtracting one date or the other but both from TODAY() then the counter will never stop.

@Zvarga Your question really isn't off topic. It sounds to me like you are wanting to stop a days counter once a certain action has been completed which is pretty much the same as what the original poster is looking for.

In your case though you are not entering a date but changing a status instead. This means you will need to set up a Record A Date automation to record the date when the status changes. From there it would be the same principal formula pulling in either TODAY or the recorded date.