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

CRUSJ · 10/16/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.

Paul Newcome · 10/17/22

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 · 10/21/22

@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 · 10/21/22

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

CRUSJ · 10/23/22

@Paul Newcome<\/a> <\/p>

Hi Paul, Thanks for answering! So I tried this and I am getting #InvalidDataType.<\/p>

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.<\/p>

Here's the formula with its actual Sheet data input:<\/p>

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

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<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/DJ8ZESXW6D6H\/smartsheet-screenshot-8.png<\/a><\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/KYPGI78N0L5S\/smartsheet-forum-9.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" \\nHi Paul, Thanks for answering! So I tried this and I am getting #InvalidDataType.\\nI 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.\\nHere's the formula with its actual Sheet data input:\\n=MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = \\\"はい Yes\\\"))) - MAX(0, MAX(COLLECT([日付 Date]:[日付 Date], [入り口ドアベル E Doorbell]:[入り口ドアベル E Doorbell], @cell = \\\"いいえ No\\\")))\\nUltimately 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\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DJ8ZESXW6D6H\\\/smartsheet-screenshot-8.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DJ8ZESXW6D6H\\\/smartsheet-screenshot-8.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/KYPGI78N0L5S\\\/smartsheet-forum-9.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/KYPGI78N0L5S\\\/smartsheet-forum-9.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\"}]","format":"rich","dateInserted":"2022-10-21T02:04:33+00:00","insertUser":{"userID":152523,"name":"CRUSJ","title":"","url":"https:\/\/community.smartsheet.com\/profile\/CRUSJ","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T03:24:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/348104#Comment_348104","embedType":"quote"}"> 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 · 10/24/22

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 · 10/24/22

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 · 10/25/22

我想我看到这个问题。你插入额外的closing parenthesis after "Yes". There should only be two there. Remove one and see if that helps.

CRUSJ · 11/16/22

@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 · 11/16/22

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

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

Answers

Help Article Resources

Categories