Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 29th 05, 01:58 PM
DKerr
 
Posts: n/a
Default Shading row with weekend dates


Hi,

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

Thanks


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=471728


  #2   Report Post  
Old September 29th 05, 02:36 PM
Dave Peterson
 
Posts: n/a
Default

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)

DKerr wrote:

Hi,

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

Thanks

--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=471728


--

Dave Peterson
  #3   Report Post  
Old September 29th 05, 02:37 PM
Dave O
 
Posts: n/a
Default

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.

  #4   Report Post  
Old September 29th 05, 02:38 PM
bj
 
Posts: n/a
Default

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.)
"DKerr" wrote:


Hi,

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

Thanks


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=471728


  #5   Report Post  
Old September 29th 05, 02:40 PM
Jonathan Cooper
 
Posts: n/a
Default

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



"DKerr" wrote:


Hi,

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

Thanks


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=471728




  #6   Report Post  
Old September 29th 05, 02:41 PM
Stefi
 
Posts: n/a
Default

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:


Hi,

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

Thanks


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=471728


  #7   Report Post  
Old September 29th 05, 06:12 PM
David McRitchie
 
Posts: n/a
Default

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

"Dave Peterson" wrote
Ps. I like this custom format for dates like this:
mm/dd/yyyy*_dddd
(Underscore represents a spacebar)



  #8   Report Post  
Old September 29th 05, 07:50 PM
Dave Peterson
 
Posts: n/a
Default

There's a lot to remember on that page!

Another one that is sometimes useful:

mm/dd/yyyy<ctrl-jdddd

(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.)


David McRitchie wrote:

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

"Dave Peterson" wrote
Ps. I like this custom format for dates like this:
mm/dd/yyyy*_dddd
(Underscore represents a spacebar)


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dates and shading Ben New Users to Excel 2 September 21st 05 03:12 PM
2 digit year in dates return 19xx not 20xx moranbo Excel Discussion (Misc queries) 1 September 7th 05 01:44 AM
Using dates for x-axis values as string instead of creating a scale cs_weirdo Charts and Charting in Excel 2 June 17th 05 12:20 AM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM
due dates Niki New Users to Excel 4 January 10th 05 05:11 PM


All times are GMT +1. The time now is 10:47 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017