1

I know this has been asked before, but I must be missing something. I want to put a border above all rows that contain "Monday".

enter image description here

The formula is =$B$3="*Monday*"

I thought it might be because the value of the cell is actually "4/18/2016" but I manually changed it to "Monday" and the rule still didn't fire. What am I doing wrong?

Community
  • 1
THE JOATMON
  • 1,878
  • 9
  • 49
  • 81

3 Answers3

2

Having the formula pointing on $B$3 will always be true or false, if $B$3 is Monday or not. You need to make the row a relativ reference, ie the formula should be something like =IF($B3="Monday";TRUE;FALSE).

The omitted $ in front of the row will make this formula look into the first column in every row, thus making a border around all mondays.

In order to improve the formula and assuming that you have a date value in your date column, I would recommend to use the function WEEKDAY() and to check for the numeric value of the weekday (see Microsoft Help).

Vince42
  • 302
  • 1
  • 7
  • 1
    FWIW When using conditional formatting it is not needed to have the IF() wrapper that returns TRUE/FALSE. Simply `$B3="Monday"` will do as it will return the needed TRUE/FALSE. But your explanation is well done. – Scott Craner Apr 13 '16 at 18:09
  • 2
    This is absolutely true, but I met many people, who were not able to understand the boolean implication of the short version, which is why I forced myself to always write the "long version". :) – Vince42 Apr 13 '16 at 18:12
1

Use the WEEKDAY() function.

Syntax: WEEKDAY(serial_number,[return_type])

Use this formula in your conditional format, assuming column A contains your dates.

=WEEKDAY(A1,2)=1

With the return type of 2, it starts the week with Monday being the 1st day. So the formula will identify the weekdays that are equal to 1 then format them with your rule.

CharlieRB
  • 22,566
  • 5
  • 56
  • 105
1

The problem was two-fold. I fixed the formula to be =$B3="Monday". I also had to create an additional column with the formula of =TEXT(C3,"dddd") so that the value was actually "Monday". As it was before, the content of the cell being "2016/4/18", it was only displaying Monday due to the cell format. This never triggered the condition.

Here is the result with Monday replaced by Saturday and Sunday.

enter image description here

THE JOATMON
  • 1,878
  • 9
  • 49
  • 81
  • Glad you were able to figure it out. For future reference, the only thing I want to point out is that your question does not say anything about needing to add a column and convert the date to a text day. Those types of details are important to get specific helpful answers. Please be more specific in future questions. – CharlieRB Apr 13 '16 at 19:30
  • That was the *answer*. I didn't know I needed to do that when I asked the question. – THE JOATMON Apr 13 '16 at 19:34
  • Updated my answer, maybe it's more clear now? – THE JOATMON Apr 13 '16 at 19:37
  • Thank you for clarifying. Just appeared to be additional information after the fact. Well done. – CharlieRB Apr 13 '16 at 19:38