1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by mariaa33, Nov 2, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    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.
     
  2. chaostheory

    chaostheory

    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
     
  3. chaostheory

    chaostheory

    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
     
  4. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Second one worked...WOW how simple. Thanks
     
  5. mariaa33

    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.
     
  6. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Nevermind. it is working now
     
  7. chaostheory

    chaostheory

    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
     
  8. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/960071

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice