Conditional format with multiple indirects

pilot · Feb 22nd, 2002, 02:29 PM

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 · Feb 22nd, 2002, 06:11 PM

Quote:

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 · Feb 22nd, 2002, 07:47 PM

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 · Feb 23rd, 2002, 11:25 AM

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 · Feb 23rd, 2002, 07:27 PM

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 · Feb 24th, 2002, 01:37 PM

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 · Feb 25th, 2002, 12:40 PM

Quote:

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 · Feb 25th, 2002, 12:50 PM

Quote:

On 2002-02-25 11:40, Russell Hauf wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 22nd, 2002, 02:29 PM	#1
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	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.

Feb 22nd, 2002, 07:47 PM	#3
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	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.

Feb 23rd, 2002, 11:25 AM	#4
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	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

Feb 23rd, 2002, 07:27 PM	#5
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	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 ]

Feb 24th, 2002, 01:37 PM	#6
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	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.