# Creating a formula to track number of days but with a catch

Options
✭✭
edited 10/17/22

So I have been tasked with creating a formula that tracks the number of days an element is "No". However, there are multiple forms submitted each day so I do not want to count the number of times a "No" is selected. It needs to be a formula that counts the consecutive days but then resets to 0 if the element has a "Yes" selected instead.

I know that there will be a COUNTIF(DATE in here but im not sure how to avoid counting the number of entries of "No" instead of the consecutive days.

So based on the screenshot--Element 1 should be 4 days, Element 2 should be 0 days and Element 3 should be 4 days. Essentially once an element receives a "Yes" that means the item is up and running again and the formula should reset to a 0.

Tags:

• ✭✭✭✭✭✭
Options

You are going to need something like this:

=MAX(0, MAX(COLLECT(Date:Date, [Element 1]:[Element 1], @cell = "Yes")) - MAX(COLLECT(Date:Date, [Element 1]:[Element 1], @cell = "No")))

• ✭✭
Options

Hi Paul, Thanks for answering! So I tried this and I am getting #InvalidDataType.

I did break the formula in half to make sure they functioned correctly on their own; they are returning a 0 if all the cells show "No" or "Yes". However, if there is a "No" AND a "Yes" in the column, it shows as #Unparseable.

Here's the formula with its actual Sheet data input:

=MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = "はい Yes"))) - MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = "いいえ No")))

Ultimately the formula should be returning a 5 based on the below screenshot because E Doorbell has been at "No" for 5 days in a row

• ✭✭✭✭✭✭
Options

Make sure the Date column is set as an actual date type column.

• ✭✭
Options

Hi Paul--I checked and the date column is indeed formatted to a date

• ✭✭✭✭✭✭
Options

If both date columns are in fact set as date type columns, then double check the data itself to ensure it is date actual dates. How exactly are the dates being entered?

• ✭✭
Options

Hi Paul--I have attached a screenshot of the columns that I am working with. The date column is set to an actual date type column. The other two columns are just text/number type. The dates are being entered on the coinciding exactly as they appear here.

In this example, I would want the [Formula Test] column to show a 2 because the [E Doorbell] column has had "No" selected 2 days in a row. I think the complicated part of this is that multiple rows will be submitted per day and I need it to only count 1 "No" per day. And then it needs to reset to 0 if "Yes" is selected.

I'm wondering if there is instead a workflow solution to this? I think this may be too complex for a formula, if not impossible in Smartsheet entirely.

• ✭✭✭✭✭✭
Options

I think I see the problem. You inserted an extra closing parenthesis after "Yes". There should only be two there. Remove one and see if that helps.

• ✭✭
Options

Hi Paul,

I had to take a break from this to refocus on some other priorities so I apologize for the late reply. Although you may have been happy to be done with this particular problem! I took that parentheses out but am getting Invalid Operation now. Here is the formula:

=MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = "はい Yes")) - MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell, ="いいえ No"))))

• ✭✭✭✭✭✭
Options

The break may have been just what I needed though. Haha. I think I see the issue now:

=MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = "はい Yes")) - MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell, ="いいえ No")))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!