Prev: Sum
Next: How do I recover an Excel document saved not saved as
From: Connie Martin on 4 Jan 2010 12:56
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

"Harald Staff" wrote:

> 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
>
>
> "Connie Martin" <ConnieMartin(a)discussions.microsoft.com> wrote in message
> news:7A8AFA01-2C11-4AD4-9803-EA101041FCBF(a)microsoft.com...
> > 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
>
> .
>
From: Connie Martin on 4 Jan 2010 14:40
Wow! So many ways of doing this. I like this one too! Thank you, Dave.

"Dave Peterson" wrote:

> 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.
>
> Connie Martin wrote:
> >
> > 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
>
> --
>
> Dave Peterson
> .
>
From: Connie Martin on 4 Jan 2010 14:43
I am amazed at how many ways there are to do this. Thank you for responding,
Fred. I will print all these. Connie

"Fred Smith" wrote:

> 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" <ConnieMartin(a)discussions.microsoft.com> wrote in message
> news:7A8AFA01-2C11-4AD4-9803-EA101041FCBF(a)microsoft.com...
> > 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
>
> .
>
From: Chip Pearson on 4 Jan 2010 21:27

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]













On Mon, 4 Jan 2010 08:48:01 -0800, Connie Martin
<ConnieMartin(a)discussions.microsoft.com> wrote:

>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]
First  |  Prev  | 
Pages: 1 2
Prev: Sum
Next: How do I recover an Excel document saved not saved as