Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 4th 10, 05:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 251
Default Date list excluding weekends

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

  #2   Report Post  
Old January 4th 10, 05:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 449
Default Date list excluding weekends

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" wrote in message
...
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


  #3   Report Post  
Old January 4th 10, 06:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Date list excluding weekends

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.
--
David Biddulph

"Connie Martin" wrote in message
...
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



  #4   Report Post  
Old January 4th 10, 06:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Date list excluding weekends

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
  #5   Report Post  
Old January 4th 10, 06:07 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default Date list excluding weekends

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" wrote in message
...
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




  #6   Report Post  
Old January 4th 10, 06:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 251
Default Date list excluding weekends

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" wrote in message
...
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


.

  #7   Report Post  
Old January 4th 10, 06:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 251
Default Date list excluding weekends

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

"David Biddulph" wrote:

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.
--
David Biddulph

"Connie Martin" wrote in message
...
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



.

  #8   Report Post  
Old January 4th 10, 08:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 251
Default Date list excluding weekends

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
.

  #9   Report Post  
Old January 4th 10, 08:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 251
Default Date list excluding weekends

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" wrote in message
...
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


.

  #10   Report Post  
Old January 5th 10, 03:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7,247
Default Date list excluding weekends


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
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]


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
Date/Time Difference Excluding Weekends & Holidays Paula D Excel Worksheet Functions 3 June 29th 12 11:15 PM
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 10 February 9th 12 08:34 PM
Calculate End Date (excluding weekends and holidays) guitaronmyback Excel Worksheet Functions 6 May 6th 09 10:17 PM
Date Formula Excluding Weekends Dvinechild Excel Discussion (Misc queries) 3 April 2nd 08 09:10 PM
Calculate A Date Excluding Weekends And Holidays travelersway New Users to Excel 1 February 8th 06 09:56 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017