Problem with conditional formatting

It seems like any of those formulas would work.except this one IF(WeekToday1 = Week2; "1"; "0")) which places the number as a text value. Can you share the sheet with me so I can test it out for you?

Share it with mwilday@lapu.edu and I will see if I can get it working for you.

Feel free to make a copy and remove all the assignments.

Try something like this (which also allows you to remove the columns DateToday and WeekToday)...

=IF(WEEKNUMBER(TODAY()) = Week@row, 1)

@Paul Newcome Nice simplification. I just don't understand why the conditional formatting isn't working. That is odd.

@Mike Wilday I tried to share the sheet but my companies policy does not seem to allow sharing outside of the company. I guess I could export to excel and send it to you, but that would defeat the purpose since you can not see formulas etc., let me know if I should do that anyway.

@Paul Newcome Thank you for the simplification. It works as intended but the conditional formatting stil does not.

As per the returned value from your formula I edited the condition to the following

I feel like something strange is going on here 🤨

If the formula used to generate the 1 in the first screenshot had quotes around the 1 ("1" instead of 1), then I would lean towards Mike's first comment.

If you are not using quotes and are in fact generating a number, then using the criteria of "is equal to" SHOULD work (tested and confirmed).

Here is something else that can be tried: Instead of defining specific criteria

Try clicking on "select from a list" to bring up a list of all values that are currently present in the column. Then you can select the 1 from the list and it should work from there.

I do know that this works because I use it very frequently to apply different formats to different levels of hierarchy.

If neither of these work, then I suggest reaching out to Support as it would seem that something is broken.

The quotes was just me testing to see if could get to work in an other way.

I have test with select from a list earlier but tried it once again now, unfortunately with the same result. I will reach out to Support.

Thank you for your time and patience

And thank you @Mike Wilday

Happy to help. 👍️

Feel free to revisit this thread once you hear back from support with what they find and what your solution was.

@Leonardo Bello Try Paul's suggestion of choosing the actual options that are in the spreadsheet. See if that produces some results. Also sometimes it helps to delete all conditional formatting and recreate it.

@Mike Wilday I did same result.

When you click on the format link, does it actually show the blue background and black text in the little dropdown options? If it does, then you should probably contact support as something very strange is going on.

@Mike Wilday @Paul Newcome FYI

I contacted support and this is the response I got

...it appears this is not recognizing the 1 as a number or as text. The way to make the conditional formatting rule apply is by using the formula: =IF(WeekToday1 = [Week2]@row, "1", "0").

This will make the 1 be treated as Text and can be set as a rule within your Conditional Formatting. I recommend using the “Contains” rule and it will work as expected. See example pic below.

I kept the simplified formula that Paul suggested but I hanged the return value to text and changed the condition to look for text as well and now it works

I have however asked if there is a reason/explanation to why the number is not recognized by the conditional formatting. Because I actually created a intermittent solution using flags and the formula for that (=IF(ThisWeek11 = 1; 1; 0)) works with out problems when checking the number value.

Thanks for the update. I still don't understand exactly what is going on with your sheet though. I use numbers to base my conditional formatting on rather frequently.

My concern with having to change it to text is that means more steps/complexity if you were wanting to use that value as an actual number in other calculations.

And my other concern is using the "contains" criteria for your Conditional formatting. I understand that in this particular application, you are only using ones and zeros, but what if I was going out to a number say 25 and wanted different formatting for each number?

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, and 21 would all end up having the same formatting because they all contain "1". I wouldn't be able to have unique formatting for each of those numbers.

But... While I don't feel that Support has gotten to the root of the problem and that something is actually broken... At least you have a solution that will work for you.