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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,996
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,129,367
Messages
5,635,853
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top