Shading row with weekend dates

D

DKerr

Hi,

I would like to create a spreasheet that will have the dates for eac
day in the current month showing from A1 to A31 with the rows wit
weekend dates shaded grey. How would I use conditional formatting to d
this? or is there another way?

Thank
 
D

Dave Peterson

Select your range (a1:A31) and with A1 the activecell:

format|Conditional formatting
formula is:
=WEEKDAY(A1,2)>5
and give it a nice shade.

Ps. I like this custom format for dates like this:

mm/dd/yyyy*_dddd
(Underscore represents a spacebar)
 
D

Dave O

In conditional formatting, set the condition to "Formula Is" and use
this formula:
=OR(WEEKDAY(A3)=7,WEEKDAY(A3)=1)

.... where A3 is the cell with the date. The WEEKDAY function returns
the date's day number within the week; above, 7 = Saturday and 1 =
Sunday. You can change that if you prefer- see Help.
 
B

bj

I would use conditional formating
select the range you wish to format
<format><conditional formating>
change "Cell value is" to "Formula is"

formula is
=or(weekday($A1)=1,Weekday(A$1)=6)
and set format pattern to your wanted shade
(Note sometimes I have to go back into the conditional formating screen and
get rid of unwanted quote marks.)
 
J

Jonathan Cooper

Select your rows: 1:31, making sure that A1 is the active cell.

Use conditional formatting

Formula is:
=IF(WEEKDAY($A1,2)>=6,TRUE,FALSE)
Picke the color you want it shaded
 
S

Stefi

Select column A, then Conditional formatting/Formula
Enter in the formula field =WEEKDAY(A1;2)>5
Click Format and choose a color!
Regards,
Stefi


„DKerr†ezt írta:
 
D

David McRitchie

Hi John,
Good idea on date formatting, I skipped over yours when
I saw you'd got there first (BTW, same formula), not
really looking at your date format with the space fill so it is
justified on both left and right sides. I'll use that with ddd instead.
Well actually I'd use yyyy-mm-dd*_ddd (_ used for spacebar)
I see I already copied your suggestion to my datetime page
from 2001-10-20 maybe I'll remember it this time.

Had to delete mine because you beat me. Anyway second
chance so I did also have link for additional information on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
and I'll even include Chip Pearson's this time, different information
http://www.cpearson.com/excel/datetime.htm
 
D

Dave Peterson

There's a lot to remember on that page!

Another one that is sometimes useful:

mm/dd/yyyy<ctrl-j>dddd

(hit ctrl-j instead of typing <ctrl-j>)

set the cell for wrap text and adjust the rowheight and columnwidth manually.
(autofitting won't work nicely.)
 
Top