Conditional format with multiple indirects

[pilot]

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.

 

[Russell Hauf]

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

 

[pilot]

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.

 

[Russell Hauf]

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

 

[pilot]

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

 

[pilot]

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.

 

[Russell Hauf]

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.

 

[Aladin Akyurek]

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