Stop Inactive Count / Capture Days Counted

Michelle Maas

I have two formulas I am trying to work out. For background information regarding the sheet, we have [Start] column capturing the date the enquiry came in and [Finish] column for the date the sale was completed. We also have another column [Expected Close Date] that is used to estimate when we expect the sale to be completed and this matches the [Finish] date column when the row (enquiry) is archived to another sheet. Sometimes the [Expected Close Date] column is blank. We have an [Inactive Count] column using this formula:

=IF(Finish@row = "", "", NETWORKDAY(Finish@row, TODAY()))

I want to adapt this formula to stop the [Inactive Count] column from continuing to count the days when the Sales Stage is complete. The trigger for this is when the [Sales Stage] column is changed to any of the following allocations:

[4 - Closed Won]

[5 - Closed Lost]

[6 - Closed No Bid]

If the enquiry is reopened, I need it to continue counting as before until closed again.

The second formula I need is to track the amount of days between the sales stage Quote to outcome, i.e. closed. We are interested in knowing how long it takes for us to finalise each sale from Quote to Closed. I can then tweak the formula to track other stages if needed. This could be done in a column in the Pipeline or in a metrics sheet, whichever works best.

[2 - Quote / Proposal]

[3 - Pending Outcome]

[4 - Closed Won]

[5 - Closed Lost]

[6 - Closed No Bid]

Appreciate your help with this.

DavidMMM

First formula: I think the easiest way to do this is to frontload your IF with another IF/OR:

=IF(OR(CONTAINS("4", [column]@row), CONTAINS("5", [column]@row), CONTAINS("6", [column]@row)), "", NETWORKDAY(Finish@row, TODAY()))

This searches the column at each row to see if the text string contains either a 4 or a 5 or a 6. If present, "", else, NETWORKDAY as you previously had it.

The numbers are in quotes because they are part of a longer string. If you were to just have the numbers in the column, no other text, you could remove the quotes.

——

Second formula: I recommend adding an automation that stamps a date if status changes to 2-Quote into a net new helper column, maybe called "QuoteDate"

=IF(NOT(ISBLANK([QuoteDate]@row)),

^ first looking for a date in that QuoteDate column,

OR(CONTAINS("3", [column]@row), CONTAINS("4", [column]@row), CONTAINS("5", [column]@row), CONTAINS("6", [column]@row)),

^ then looking for the 4 (?) closed stages,

[Finish]@row-[Start]@row)

^ (this you could wrap in a NETWORKDAY if you want) which would generate the difference between the Closed date and the Start date, given status was previously 2-Quote.

Thus:

=IF(NOT(ISBLANK([QuoteDate]@row)), OR(CONTAINS("3", [column]@row), CONTAINS("4", [column]@row), CONTAINS("5", [column]@row), CONTAINS("6", [column]@row)), [Finish]@row-[Start]@row)

Does this have the effect you are looking for?

Michelle Maas

Thanks @DavidMMM I realise the only way to do this reporting is to create helper columns in the sheet. In this case I have a column for the quote issue date, and another column for the conversion rate counting days between the quote issue date and the closed won date. Appreciate your help anyway.