Date list excluding weekends

[Connie Martin]

If I have a column of dates (365 days) from Jan. 1 to Dec. 31, is there a way
to delete all weekends without the long, manual way of doing it? Connie

 

[Harald Staff]

Use the WEEKDAY formula to spot saturdays and sundays. Then use
filter/autofilter to hide them, copy visible cells to another location.

HTH. Best wishes Harald

 

[David Biddulph]

Use a helper column =LEFT(TEXT(A1,"ddd"))
Data/ Autofilter to select those where the entry is S
Select those rows, and Delete row
Disable the Autofilter, & delete the helper column.

 

[Dave Peterson]

You could use a helper column and return the weekday for that date:

=WEEKDAY(a1,2)

Then drag this formula down the column.

Apply Data|filter|autofilter to this helper column
and show the values > 5 (6 and 7 will be the weekends)

Then delete the visible rows

And remove the filter.

================
But maybe even better is to create the list without the weekends.

Put the start date in A1 (say)
Rightclick on the autofill button (the bottom right corner of the selection
indicator.
And drag down

But choose "Fill Weekdays"

There are lots of options you can get when you rightclick and drag on that
autofill button.

 

[Fred Smith]

One option is to create the column without weekends, using a formula like:
=if(weekday(a1)>5,a1+3,a1+1)
Copy down

Regards,
Fred

 

[Connie Martin]

Thank you, David. This is very easy. Don't know why I didn't think of it.
Thanks so much. Connie

 

[Connie Martin]

Thank you, Harald. I wish these formulas were more on the end of my fingers,
if you know what I mean. Shall print this so I don't forget it! Connie

 

[Connie Martin]

Wow! So many ways of doing this. I like this one too! Thank you, Dave.

 

[Connie Martin]

I am amazed at how many ways there are to do this. Thank you for responding,
Fred. I will print all these. Connie

 

[Chip Pearson]

If you already have your list in place, you can use the following code
to delete rows that contain Saturday or Sunday. Change TestCol to the
column letter that contains the date to be tested. Change StopRow to
the row number that processing should stop, working from the bottom
up. So, if you want to preserve the first 10 rows of the worksheet,
set StopRow to 11. Change WS to the name of the worksheet than
contains the data. Then run the code:

Sub DeleteWeekends()
Dim WS As Worksheet
Dim RowNdx As Long
Dim TestCol As String
Dim LastRow As Long
Dim StopRow As Long

TestCol = "B" '<<< Column to test dates
StopRow = 3 '<<< Row number to stop at moving upwards
Set WS = Worksheets("Sheet1") '<<< Which worksheet.
With WS
LastRow = .Cells(.Rows.Count, _
TestCol).End(xlUp).Row
End With

For RowNdx = LastRow To StopRow Step -1
If Weekday(WS.Cells(RowNdx, TestCol).Value, _
vbMonday) >= 6 Then
WS.Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


If you want to create a new list of dates that exclude weekends, enter
the starting data in some cell, say A1. Then, in A2, enter

=WORKDAY(A1,1)

and copy this formula down for as many rows as you need. If you are
using Excel 2003 or earlier, you'll need to load the Anlaysis Tool
Pack. Go to the Tools menu, choose Add-Ins, and put a check next to
"Analysis Tool Pak". This step is not necessary in Excel 2007 and
later.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

If I have a column of dates (365 days) from Jan. 1 to Dec. 31, is there a way
to delete all weekends without the long, manual way of doing it? Connie

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]