Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date/Time Difference Excluding Weekends & Holidays | Excel Worksheet Functions | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Calculate End Date (excluding weekends and holidays) | Excel Worksheet Functions | |||
Date Formula Excluding Weekends | Excel Discussion (Misc queries) | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel |