[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Excel conditional formatting formula for cell containing

I have an Excel spreadsheet that I tried to use conditional formatting. I am using a formula for this as opposed to the preset of finding cells containing something. I never could get that one to work. But I'm having a hard time using a formula for some reason. Here is what I'm trying to do:
assume I have a big column of dates in basically the following format:
Saturday October 1, 2011
Sunday, October 2, 2011
Monday, October 3, 2011
I want the Saturday and Sunday date cells to be changed to red type, while the Monday cell would be changed or (or should I really say) left as a black type.

With conditional formatting they do have an option to select cells containing. But whereas sites-Saturday nothing happens and I'm not sure why. That's why I tried a formula but I could not figure out a symbol to use in a formula for a cell that contains a text string.
Appreciate any help in this as I'm sure it is easy, but I'm having difficulty with it.
0
bob733
Asked:
bob733
  • 5
  • 3
  • 2
2 Solutions
 
barry houdiniCommented:
If these are actual dates the try using a formula like this in conditional formatting

=WEEKDAY(A2,2)>5

Apply red font

regards, barry
0
 
barry houdiniCommented:
See example atttached - I had to add another condition so that blank cells weren't formatted, so I used this

=AND(A1<>"",WEEKDAY(A1,2)>5)

regards, barry
format-dates.xls
0
 
barry houdiniCommented:
Note: if data is text rather than actual dates then switch to this formula

=COUNTIF(A1,"*Sat*")+COUNTIF(A1,"*Sun*")

barry
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
sah18Commented:
I've attached an example spreadsheet which uses conditional formatting for what you want to do.  This is in Excel 2007/2010 format.  There are two tabs -- the first showing a text solution, and the second showing a date solution.  If you'd like to post your actual spreadsheet, I can help setup the conditional format for you in your actual spreadsheet, if that would be helpful.

Just let me know if you have any questions!
-sah18
example-conditional-formatting-s.xlsx
0
 
sah18Commented:
Also, a little additional info, in order to see the rules applied in my example attached above, just click A to highlight all of column A, then click on Conditional Formatting --> Manage Rules.  Here you will see the specific rules I applied to get the formatting to display correctly.  If you are on the Text worksheet, you will see the text rule there.  If you are on the Dates worksheet, you will see the dates rules there.  If you do not first highlight column A, then you won't see the rules at all -- just making sure you knew this!

Take care,
sah18
0
 
barry houdiniCommented:
Hello sah18,

Your conditional formatting for dates only works co-incidentally. You have set it up so that A1 is formatted based on the date in A2 - A1 is formatted if A2 is a Sunday or a Monday (when using WEEKDAY with a second argument of 2 Mondays return 1).

So what you have works as long as the dates are in order. Try putting in random dates out of order.....or extending the sequence of dates so that the last one is 23rd October - it won't be formatted because there is no date in the next cell.

regards, barry
0
 
bob733Author Commented:
Gentlemen. I took both of your conditional formatting examples and applied them to my sheet.
It appears that varies formula does work regardless of what date I start on. As you can see I took your formulas and applied them to a Tuesday start date. I believe I have copied your formulas and modified the columns correctly, however I could have made a mistake that would cause days other than Sunday or Saturday to appear in red.

I attach the file because I thought both of you absolute experts in the Excel application would be interested in seeing what it's doing.  
example-conditional-formatting-s.xlsx
0
 
barry houdiniCommented:
When you apply conditional formatting the formula used is applied to the first cell in the "Applies to" range, so in columns I and K, where you have the formulas I suggested, the "Applies to" ranges are I2:I18 and K2:K18 respectively, so the formula correctly refers to I2 or K2, the first cell in the "Applies to" range and you get the correct results.

In the other columns, though, the formulas aren't applied correctly, e.g. in column E the "Applies to" range is the whole column E:E...so the formula should refer to the first cell, E1....but it's this

=IF(LEFT(E2,1)="S",TRUE)

So that means that E1 is formatted if E2 starts with an "S", E2 is formatted if E3 starts with an "S" etc.

These should be corrected so that you are formatting the cell based on the content of that cell.

In columns A and C, though, the formula is also incorrect, WEEKDAY(A1,2) gives a 1 on a Monday and a 7 on a Sunday....so the formula is actually formatting cells where the cell below is a Monday or a Sunday. Of course this means that if you have the dates in order it will format Saturdays and Sundays as required (because the formula is looking at the cell below)......but any dates out of order, or where the last date in the column is a weekend date, won't be correctly formatted. In this case the formula can be changed to a single one as I suggested.

see attached where I have generated random dates..and all formulas are working, using a combination of approaches. Press F9 key to re-generate dates.

PS....also included one more option...this will format weekend dates whether text or date - assuming that the day ("Sat" or "Saturday" etc.) is included somewhere in the text format,. See columns J and L, same formula, different format

Formula: =COUNT(FIND("Sat",TEXT(J5,"ddd")),FIND("Sun",TEXT(J5,"ddd")))*(J5<>"")

regards, barry
27402179.xlsx
0
 
bob733Author Commented:
I split the points somewhat because I do appreciate sah18 pitching in.  Barry, you had the most complete and best answer overall and I really appreciate your help.
0
 
sah18Commented:
Glad you got things working & I appreciate the points!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now