Problem with conditional formatting

Leonardo Bello

I am trying to create a sheet for a moving task that changes each week depending on week number. The idea is to highlight and send out a reminder email to the person that has the task a certain week.

To achieve this I have use the formula following

TODAY() in cell DateToday1

WEEKNUMBER(DateToday1) in cell WeekToday1

In the cells for the Column TRUE is have tried different formulas because I think here lays the problem

IF(WeekToday1 = Week2; 1; 0))

IF(WeekToday1 = Week2; "1"; "0"))

IF(WeekToday1 = Week2; 1; " "))

IF(VALUE(WeekToday1) = Week2; 1; 0)

VALUE(IF(WeekToday1 = Week2; 1; 0))

Obviously the formula on some level works because it is returning a value I expect but when using conditional formatting I am not getting it to work. I have tried different formatting rules depending on which of the above formulas I have used but nothing has worked for me.

Mike Wilday

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.

Mike Wilday

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

Paul Newcome

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

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

Mike Wilday

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

Leonardo Bello

@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 🤨

Paul Newcome

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.

Leonardo Bello

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

Paul Newcome

Happy to help. 👍️

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

Mike Wilday

@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.

Leonardo Bello

@Mike Wilday I did same result.

Mike Wilday

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.

Leonardo Bello

@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.

Paul Newcome

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.

Leonardo Bello

@Paul Newcome

Here is the answer I got from support regarding why numbers are not being recognized

....I was able to reproduce this issue using the screenshots from your community post. In my testing this happens when a column is named either "true" or "false". As this doesn't appear to be expected behavior I am escalating this case.

Andrée Starå

@Leonardo Bello

That's good to know! Thanks for sharing!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD