Solved: Excel 2007: conditional format when date displayed as format d

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
I am setting up a spreadsheet for the year and want to use it year over year. I have the cell set up with the date (1/1/2011) with a format of d so that it displays the date as Sat, 1/2/2011 - Sun, etc. Since I want to use this year after year with minimal work and I want to highlight the cells that are equal to Sat and Sun, how would I set up that formula. I tried the basic =A1="Sat" and without quotes but it just returns #NAME. I know working with dates in formulas is different but can't figure out what I need to use.
 
Joined
May 18, 2007
Messages
114
Well for a quick reply, since the whole date as a string starts with Sat try using =Left(A1,3)= "Sat" instead of just "Sat". Not sure if you can do that, but that would work in a cell
 
Joined
May 18, 2007
Messages
114
Actually scratch that, this works.

Change the conditional format to formula then use this:

=WEEKDAY(A1)=1 for Sunday
=WEEKDAY(A1)=7 for Saturday

That will change the color of the cells
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
I am still not getting this to work for the shading the cells under that column. I also want to highlight the cells below based on that cell and be able to copy the formula to adjacent columns.
 
Joined
May 18, 2007
Messages
114
I am just assuming here but if your calendar is just cells with dates in them in a rectangular fashion. Highlight the whole calendar and type in that formula, it changes the cell value in the formula for each cell that it applies to automatically. Then it should shade all saturdays and sundays in the range you choose for conditional formatting.

Edit: Hehe...no worries :) Glad its working for you
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top