Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Conditional format with multiple indirects

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please help with this formula.
    Year is in $AE$2
    Month is in $A5, $A7, $A9... and is 3 characters text as JAN, FEB, MAR...
    Day (1 - 31) is in B$4:AF$4
    I want to conditionally format every cell (B5:AF27) if date (obtained using INDIRECTs) is Sat, Sun or invalid (April 31st). For example, X7 would be shaded because it is Feb 23, 2002 which is Saturday. Many thanks.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 13:29, pilot wrote:
    Please help with this formula.
    Year is in $AE$2
    Month is in $A5, $A7, $A9... and is 3 characters text as JAN, FEB, MAR...
    Day (1 - 31) is in B$4:AF$4
    I want to conditionally format every cell (B5:AF27) if date (obtained using INDIRECTs) is Sat, Sun or invalid (April 31st). For example, X7 would be shaded because it is Feb 23, 2002 which is Saturday. Many thanks.
    Don't know why you need INDIRECT...
    If you put the numbers 1-12 in your MONTH cells, and format as "mmm", you can use the DATE function for your dates. At any rate, here is a formula you can use for conditional formatting:

    =MOD(WEEKDAY(B5),7)<=1

    Let me know if you can't get it to work and I can send you an example (most likely Monday, however).

    Have a good weekend,

    Russell

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please help with this formula.
    Year is in $AE$2
    Month is in $A5, $A7, $A9... and is 3 characters text as JAN, FEB, MAR...
    Day (1 - 31) is in B$4:AF$4
    I want to conditionally format every cell (B5:AF27) if date (obtained using INDIRECTs) is Sat, Sun or invalid (April 31st). For example, X7 would be shaded because it is Feb 23, 2002 which is Saturday. Many thanks.


    --------------------------------------------------------------------------------



    Don't know why you need INDIRECT...
    If you put the numbers 1-12 in your MONTH cells, and format as "mmm", you can use the DATE function for your dates. At any rate, here is a formula you can use for conditional formatting:

    =MOD(WEEKDAY(B5),7)<=1

    Let me know if you can't get it to work and I can send you an example (most likely Monday, however).

    Have a good weekend,

    Russell
    --------------------------------------------

    Don't think this will work. B5 corresponds to JAN (Row 5) 1 (Col B) but the cell is empty (or has text in it), not the date. That's why I thought INDIRECT was required.

    I knew I could change month from text to mmm formatted 1-12 but I'm trying to use data exactly as it has been provided to me, therefore prefer to make the formula handle JAN to 1, etc.

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you have your dates (however you have created them) in cells B5-whatever, then you can use the conditional format formula that I provided. If you can't get it to work, send me the workbook (or a sample) and I can get to it on Monday or Tuesday.

    -rh

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dates are not in B5:AF27. Months are in Col A and days of the month are in Row 4. Any date in the year is inferred by the Row/Col intersection, i.e. Row 7 is Feb, Col X is day 23 so X7 corresponds to 02-23-02. Appreciate your continued help. Can send file if still needed.

    [ This Message was edited by: pilot on 2002-02-23 18:29 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I figured it out, all but two aspects. Here's the Conditional Format formula in B5 (sets shaded background) if Sat or Sun.

    =WEEKDAY(DATE($AE$2,MONTH($A5),B$4),2)>5

    I did change JAN text to 1, etc. Also, invalid dates in my grid are treated as valid dates in next month (Apr 31 translates to May 1). Will post these a new questions. Thanks, Russell.

  7. #7
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 12:37, pilot wrote:
    I figured it out, all but two aspects. Here's the Conditional Format formula in B5 (sets shaded background) if Sat or Sun.

    =WEEKDAY(DATE($AE$2,MONTH($A5),B$4),2)>5

    I did change JAN text to 1, etc. Also, invalid dates in my grid are treated as valid dates in next month (Apr 31 translates to May 1). Will post these a new questions. Thanks, Russell.
    The only thing is that the weekdays for Saturday and Sunday are 7 and 1, respectively.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,074
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-02-25 11:40, Russell Hauf wrote:
    On 2002-02-24 12:37, pilot wrote:
    I figured it out, all but two aspects. Here's the Conditional Format formula in B5 (sets shaded background) if Sat or Sun.

    =WEEKDAY(DATE($AE$2,MONTH($A5),B$4),2)>5

    I did change JAN text to 1, etc. Also, invalid dates in my grid are treated as valid dates in next month (Apr 31 translates to May 1). Will post these a new questions. Thanks, Russell.
    The only thing is that the weekdays for Saturday and Sunday are 7 and 1, respectively.
    Russell,

    As a side-effect of a question pilot posted under the title "Invalid dates", I also took up (or rather "dragged into") this cond format question. See that post.

    BTW, you can give an additional param to weekday, e.g., 2, to get a different numbering of days.

    Regards,

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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