Combining IF Statements is not working with comma

CRUSJ

Hello,

My formula is as follows:

=IF(AND([日付 Date]@row < TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Red"), (IF(AND([日付 Date]@row <> TODAY(), CONTAINS("2号室 Room 2", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray"))

The goal here is to create a red circle if 1号室 Room 1 checkbox is selected in the RVE Lighting column range AND the date is today. But, the circle needs to remain in that row but then turn gray once it is no longer Today. And if 1号室 Room 1 is not selected on any day, the row should have no circle at all.

Thank you

Mike Blumenstein

Typically when you nest IF statements you would do it in this format, =IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, value-if-false))

You have an extra ( before the 2nd IF, and you closed the ) after "Red"

AS a suggestion though... for things like this that you want to leverage the date to trigger an action on the sheet, I would highly suggest staying away from the TODAY() formula as it requires an open and save action on the sheet to process today's date and the formula. For instance, if this sheet would be published on a Dashboard, the formula would not update until the next open and save, so would be out of date and the wrong color would be shown. You could set this up to light up a row/cell background to Red or Gray with a few MUCH simpler conditional formatting rules that would use the same expressions and logic as your formula above, but not need the TODAY() formula.

Just a suggestion.

CRUSJ

https://community.smartsheet.com/discussion/comment/345329#Comment_345329

Hi Mike, Thank you for the suggestion. If I cannot get this formula to work properly, I may utilize conditional formatting instead but for now I want to keep the current standard for this companies current procedures. Unfortunately using this type of conditional formatting would require 288 separate new rules in order to achieve our end goal here.

I made the edits to the formula and am no longer getting an error, however, it still is not functioning properly in regard to the colors of the circles. The top row is todays entry and all the dots are Gray. The entries in the past are all Red. However, I only failed ERV1, ERV2, ERV3, ERV4 on the first row(today). They are currently passed in the historical/lower rows., But those rows all show red dots. The dot should only show up in the column when an ERV is failed in that row. Otherwise, it should be blank.

New formula:

=IF(AND([日付 Date]@row < TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Red", IF(AND([日付 Date]@row <> TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray"))

Andrée Starå

Hi @CRUSJ

I hope you're well and safe!

To add to Mike's excellent advice/answer.

This might help!

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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

Mike Blumenstein

https://community.smartsheet.com/discussion/comment/345384#Comment_345384

Are you also going to need to put 288 nested IF statements in your formula as well? I would have to see the whole sheet to understand what you are actually looking to do with this formula, as it sounds like this will be a VERY complex sheet given the range and how many variables you might have.

It looks like, since you are creating the range to be the entire column as long as a single row has either of those Rooms, they are all "true". So now it's failing back to the first part of the AND which is evaluating for Dates. In your Formula you are testing for Yesterday to be "Red" or Yesterday or Tomorrow to be "Gray". So any date that equals today would be blank.

Based on the sheet content how you have it structured, You could write this formula to be as simple as =IF([日付 Date]@row < TODAY(), "Red", IF([日付 Date]@row > TODAY(), "Gray")) and get the exact same results. ERV1-ERV4 are all looking at the same column for the CONTAINS, so as long as it contains either of those in the entire column, that part will always be true, and really only evaluates the date.

Based on your initial request, (Goal) I took some liberties to rewrite the formulas, based on a Date Column, a multi-select Column with 1号室 Room 1 & 2号室 Room 2, and I changed the CONTAINS to evaluate the row, versus the entire column.

ERV1 = If it's TODAY and 1号室 Room 1 = Red, if not today, Gray.

ERV2 = If it's TODAY and 2号室 Room 2 = Red, if not today, Gray.

ERV3 is your formula as written, only blank when Today. Red in the past, Gray in the future.

I also added a Conditional format to highlight today's date just to make it easier to see.

ERV1 =IF(AND([日付 Date]@row = TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]@row)), "Red", IF(AND([日付 Date]@row <> TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]@row)), "Gray"))

ERV2 =IF(AND([日付 Date]@row = TODAY(), CONTAINS("2号室 Room 2", [問題報告RV東照明 Reporting Issue RVE Lighting]@row)), "Red", IF(AND([日付 Date]@row <> TODAY(), CONTAINS("2号室 Room 2", [問題報告RV東照明 Reporting Issue RVE Lighting]@row)), "Gray"))

ERV3 =IF(AND([日付 Date]@row < TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Red", IF(AND([日付 Date]@row <> TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray"))

Outside of that, I would need to fully understand what you are trying to accomplish with this sheet, or better yet, reach out to Andrée he is one of our partners that can consult with you to help you out. :)