Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi gang,
    I want to put conditional formatting (shading) so that the first Monday of the month looks different.
    Looking at past threads I have figured out how to calculate the first Monday of the month.
    D5 = Nov. 30, 2009 (in date format)
    the formula is
    =D5-DAY(D5)+1+MOD(2-WEEKDAY(D5-DAY(D5)+1);7)
    the result is Nov. 2, 2009 which is the first Monday of the month
    When I put this formula in my conditional formatting for cell D5 it gets shaded and all the other cells where I copied the conditional formatting get shaded as well.
    What am I missing?
    tks
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'd try a formula like =AND(DAY(C1)<8,WEEKDAY(C1,2)=1)
    Where C1 is the cell containing the date you want examined.
    I'm attaching a sample sheet[attachment=86880:Formatting the first monday in the month.xlsx]

    From your example, I'd say that the test you are performing =D5-DAY(D5)+1+MOD(2-WEEKDAY(D5-DAY(D5)+1);7) is not a true/false test
    (it is returning a date). So it is true throughout the spreadsheet for every cell, hence every cell changes colour.

    For conditional formatting [s]with a date to work [/s]- the formula has to evaluate to a True/False result.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •