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

CRUSJ

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.

Paul Newcome

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")))

CRUSJ

@Paul Newcome

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

Paul Newcome

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

CRUSJ

https://community.smartsheet.com/discussion/comment/348104#Comment_348104

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

Paul Newcome

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?

CRUSJ

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.

Paul Newcome

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.

CRUSJ

@Paul Newcome

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"))))

Paul Newcome

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")))