is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth function for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't know if
this is possible with this set up.

--


Gary

Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may wish to
study.

I set three conditional formats, the first to pick out today, the second to
pick out days that are not in this month (such as the 30 in Feb) so that it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and A4,A5 is
simply 1,2, etc. then use these three conditions. It is important to get the
order right for them all to show. I show the formula assuming B4 is the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't know if
this is possible with this set up.

--


Gary

=WEEKDAY(DATE(YEAR(B$3),MONTH(B$3),DAY($A4)),3)

Apply the above to each cell in the matrix and then set conditional
formatting to cells where cell value is =5; as a weekend

Note: Excel will allow invalid date e.g. 31 Apr by computing the day as 1
May - the correct day number is returned.

--
Cheers
Nigel



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't know if
this is possible with this set up.

--


Gary

thanks, bob, works great. i just used the last 2 rules because i wasn't
concerned with highlighting the current day. i used that 3rd rule to
complete the border grid for all of the cells.

thanks again

--


Gary


"Bob Phillips" wrote in message
...
Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may wish to
study.

I set three conditional formats, the first to pick out today, the second
to
pick out days that are not in this month (such as the 30 in Feb) so that
it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and A4,A5
is
simply 1,2, etc. then use these three conditions. It is important to get
the
order right for them all to show. I show the formula assuming B4 is the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't know
if
this is possible with this set up.

--


Gary

Can't you include the borders in the other bits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, bob, works great. i just used the last 2 rules because i wasn't
concerned with highlighting the current day. i used that 3rd rule to
complete the border grid for all of the cells.

thanks again

--


Gary


"Bob Phillips" wrote in message
...
Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may wish
to
study.

I set three conditional formats, the first to pick out today, the second
to
pick out days that are not in this month (such as the 30 in Feb) so that
it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and
A4,A5
is
simply 1,2, etc. then use these three conditions. It is important to get
the
order right for them all to show. I show the formula assuming B4 is the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't know
if
this is possible with this set up.

--


Gary

i did, but the weekdays had no border, only the weekends.

--


Gary


"Bob Phillips" wrote in message
...
Can't you include the borders in the other bits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, bob, works great. i just used the last 2 rules because i wasn't
concerned with highlighting the current day. i used that 3rd rule to
complete the border grid for all of the cells.

thanks again

--


Gary


"Bob Phillips" wrote in message
...
Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may wish
to
study.

I set three conditional formats, the first to pick out today, the
second
to
pick out days that are not in this month (such as the 30 in Feb) so
that
it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and
A4,A5
is
simply 1,2, etc. then use these three conditions. It is important to
get
the
order right for them all to show. I show the formula assuming B4 is the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth
function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't
know
if
this is possible with this set up.

--


Gary

Gary,

Put borders round everything by default, then set the ones that meet a
condition with conditional NO borders.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i did, but the weekdays had no border, only the weekends.

--


Gary


"Bob Phillips" wrote in message
...
Can't you include the borders in the other bits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, bob, works great. i just used the last 2 rules because i wasn't
concerned with highlighting the current day. i used that 3rd rule to
complete the border grid for all of the cells.

thanks again

--


Gary


"Bob Phillips" wrote in message
...
Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may
wish
to
study.

I set three conditional formats, the first to pick out today, the
second
to
pick out days that are not in this month (such as the 30 in Feb) so
that
it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and
A4,A5
is
simply 1,2, etc. then use these three conditions. It is important to
get
the
order right for them all to show. I show the formula assuming B4 is
the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth
function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't
know
if
this is possible with this set up.

--


Gary

i just used the formula in the 3rd rule
=MOD(ROW(),1)=0
works fine.

i also added a cross-hatch pattern to rule 1 to show the non-existent dates
in the months with less that 31 days
--


Gary


"Bob Phillips" wrote in message
...
Gary,

Put borders round everything by default, then set the ones that meet a
condition with conditional NO borders.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i did, but the weekdays had no border, only the weekends.

--


Gary


"Bob Phillips" wrote in message
...
Can't you include the borders in the other bits?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, bob, works great. i just used the last 2 rules because i
wasn't
concerned with highlighting the current day. i used that 3rd rule to
complete the border grid for all of the cells.

thanks again

--


Gary


"Bob Phillips" wrote in message
...
Gary,

I do something very similar at
http://www.xldynamic.com/source/xld.CF.html#calendar which you may
wish
to
study.

I set three conditional formats, the first to pick out today, the
second
to
pick out days that are not in this month (such as the 30 in Feb) so
that
it
does mis-lead, and weekends.

Assuming that the dates in B3,C3, etc are all 1st day of month, and
A4,A5
is
simply 1,2, etc. then use these three conditions. It is important to
get
the
order right for them all to show. I show the formula assuming B4 is
the
activecell in the selection

#1 - highlight today
Formula - =B$3+$A4-1=TODAY()
Set a pattern colour
I also set the font to Bold, White for emphasis

#2 - highlight days not in month
Formula - =MONTH(B$3+$A4-1)<MONTH(B$3)
Set the font colour to white so that any test in the cell gets
hidden

#3 - highlight weekends
Formula - =WEEKDAY(B$3+$A4-1,2)5
Set the pattern colour, I use a pastel colour to be easy on the eye

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is this possible in my scenario?

this is a date table
column headings for each month, b3 has 1/1/2006, using eomonth
function
for
the rest of the months and headings formatted MMM .

in column A, i just have integers, 1 through 31.

is it possible to format cells based on weekends?

i have done it for individual months on separate sheets, but don't
know
if
this is possible with this set up.

--


Gary