Identifying Weekends

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
I am trying to identify weekends in a date range.
I know that I can use the Edit>Fill>Series option and select weekdays only but I actually want to show them on my spreadsheet but grey them out. Is there any formula to do this?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

dbloom

New Member
Joined
May 2, 2008
Messages
21
I don't know a formula to do it all at once but you can do the series fill and then do conditional formatting for cells equal to Saturday and Sunday
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,967
Use conditional formatting.

Format --> conditional formatting --> formula is: =WEEKDAY(A1,2)>=6 and apply the desired gray background if it's a weekend.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Assuming your dates start in A1,

<TABLE style="WIDTH: 113pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=151 border=0 x:str><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=151 height=17>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'to conditionally format weekends, Formula is: =WEEKDAY(A1,2)>5"></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 333pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=444 border=0 x:str><COLGROUP><COL style="WIDTH: 333pt; mso-width-source: userset; mso-width-alt: 16237" width=444><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 333pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444 height=17>to conditionally format weekdays, Formula is: =WEEKDAY(A1,2)<7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'to conditionally format weekends, Formula is: =WEEKDAY(A1,2)>5">to conditionally format weekends, Formula is: =WEEKDAY(A1,2)>5</TD></TR></TBODY></TABLE>

Edit: Well, I guess oak trees grow more quickly than palm trees.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,203
Messages
5,473,138
Members
406,847
Latest member
Meow

This Week's Hot Topics

Top