Get list of week dates

Board Regular
i want to get a list of dates excluding weekends as explained below. (assume 8/1 is a monday)

8/1
8/2
8/3
8/4
8/5
8/8
8/9 and so on....

Any help will be appreciated

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe Was

MrExcel MVP
Near the top of this code are settings you need to set!
This code will add an in-Cell Drop-Down list containing all your dates.

Sub myCalDates()
'Standard module code, like: Module1.
Dim datMyStartDate As Date, datMyNewDate As Date
Dim lngNextRow&, lngIntervalPeriod&, lngNumOfDatesToList&, lngColNum&
Dim strInCellDropDownSheetName\$, strTableOfDatesDataSheetName\$
Dim strDtIntervalType\$, strCellToGetTheDropDownList\$, strTableOfDatesColumn\$
Dim strDateTableColumn\$, strNameOfThisDateList\$, strListLocation\$

'Note: You must change the information below!
'Adjust the StartDate and the location of the DatesList data location,
'then adjust the In-Cell-Data-Validation-Drop-Down-List location below!
'*************** Default Date Information! **************************************

datMyStartDate = #8/1/2007# 'The Date to start the Date List With!

strDtIntervalType = "w" 'd=Day,w=WeekDay,m=Month,y=Year, the DropDown Date Intrerval!
lngIntervalPeriod = 1 'How many of the Interval Type to space dates by!

lngNumOfDatesToList = 31 'How many dates to list!

strTableOfDatesDataSheetName = "Sheet2" 'The Sheet Name that gets Date Data!
strTableOfDatesColumn = "A" 'The Column to use for this Date Data!

strInCellDropDownSheetName = "Sheet1" 'The Sheet Name of where the DropDown is!
strCellToGetTheDropDownList = "C6" 'The actual Cell that gets the DropDown List!
strNameOfThisDateList = "DateList" 'The name of this Date List!
'*************** Default Date Information! **************************************

On Error GoTo myNoNamedRng
ActiveWorkbook.Names("DateList").Delete

myNoNamedRng:

With Sheets(strTableOfDatesDataSheetName)
.Select
strDateTableColumn = strTableOfDatesColumn & ":" & strTableOfDatesColumn
.Columns(strDateTableColumn).ClearContents
.Columns(strDateTableColumn).NumberFormat = "[\$-F800]dddd, mmmm dd, yyyy"
.Range(strTableOfDatesColumn & 1).Select

.Range(strTableOfDatesColumn & 1).Value = datMyStartDate

For lngNextRow = 2 To lngNumOfDatesToList
.Range(strTableOfDatesColumn & lngNextRow).Value = datMyNewDate
datMyStartDate = datMyNewDate
Next lngNextRow

.Columns(strDateTableColumn).Columns.AutoFit

lngColNum = Range(strTableOfDatesColumn & ":" & strTableOfDatesColumn).Column

strMyListLocation = "=" & strTableOfDatesDataSheetName & _
"!R1C" & lngColNum & ":R" & lngNumOfDatesToList & "C" & lngColNum

RefersToR1C1:=strMyListLocation
.Range(strTableOfDatesColumn & 1).Select
End With

Sheets(strInCellDropDownSheetName).Select
Range(strCellToGetTheDropDownList).Select
With Selection.Validation
.Delete

strListLocation = "=" & strNameOfThisDateList

xlBetween, Formula1:=strListLocation
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select Date!"
.InputMessage = "Pick the DATE you want from the list here!"
.ShowInput = True
.ShowError = False
End With

With Sheets(strInCellDropDownSheetName).Range(strCellToGetTheDropDownList)
.NumberFormat = "[\$-F800]dddd, mmmm dd, yyyy"
.ColumnWidth = 28
.ClearContents
End With
End Sub

Joe Was

MrExcel MVP
For some reason the code above works for all date types except for WeekDays. This new code only works for weekdays:

Sub myWeekDays()
'Standard module code, like: Module1.
Dim datMyStartDate As Date, datMyNewDate As Date
Dim lngNextRow&, lngIntervalPeriod&, lngNumOfDatesToList&, lngColNum&, lngMyBot&
Dim strInCellDropDownSheetName\$, strTableOfDatesDataSheetName\$
Dim strDtIntervalType\$, strCellToGetTheDropDownList\$, strTableOfDatesColumn\$
Dim strDateTableColumn\$, strNameOfThisDateList\$, strListLocation\$
Dim rngMyUsedRng As Object, cell As Object
Dim myDt As Variant

'Note: You must change the information below!
'Adjust the StartDate and the location of the DatesList data location,
'then adjust the In-Cell-Data-Validation-Drop-Down-List location below!
'*************** Default Date Information! **************************************

datMyStartDate = #8/1/2007# 'The Date to start the Date List With!

lngIntervalPeriod = 1 'How many of the Interval Type to space dates by!

lngNumOfDatesToList = 31 'How many dates to list!

strTableOfDatesDataSheetName = "Sheet2" 'The Sheet Name that gets Date Data!
strTableOfDatesColumn = "A" 'The Column to use for this Date Data!

strInCellDropDownSheetName = "Sheet1" 'The Sheet Name of where the DropDown is!
strCellToGetTheDropDownList = "C6" 'The actual Cell that gets the DropDown List!
strNameOfThisDateList = "DateList" 'The name of this Date List!
'*************** Default Date Information! **************************************

On Error GoTo myNoNamedRng
ActiveWorkbook.Names("DateList").Delete

myNoNamedRng:

With Sheets(strTableOfDatesDataSheetName)
.Select
strDateTableColumn = strTableOfDatesColumn & ":" & strTableOfDatesColumn
.Columns(strDateTableColumn).ClearContents
.Columns(strDateTableColumn).NumberFormat = "[\$-F800]dddd, mmmm dd, yyyy"
.Range(strTableOfDatesColumn & 1).Select

.Range(strTableOfDatesColumn & 1).Value = datMyStartDate

For lngNextRow = 2 To lngNumOfDatesToList
.Range(strTableOfDatesColumn & lngNextRow).Value = datMyNewDate
datMyStartDate = datMyNewDate
Next lngNextRow

.Columns(strDateTableColumn).Columns.AutoFit
Set rngMyUsedRng = .Range(Cells(1, .Columns(strDateTableColumn).Column), _
Cells(lngNumOfDatesToList, .Columns(strDateTableColumn).Column))

For Each cell In rngMyUsedRng
If cell.Value = "" Then GoTo myNext
myDt = Format(cell.Value, "short date")
If (Application.WorksheetFunction.Weekday(myDt, 2) = 6 Or _
Application.WorksheetFunction.Weekday(myDt, 2) = 7) Then _
cell.Clear

myNext:
Next cell

For Each cell In rngMyUsedRng
If cell.Value = "" Then cell.EntireRow.Delete
Next cell

For Each cell In rngMyUsedRng
If cell.Value = "" Then cell.EntireRow.Delete
Next cell

lngColNum = Range(strTableOfDatesColumn & ":" & strTableOfDatesColumn).Column
lngMyBot = Range(strTableOfDatesColumn & "65536").End(xlUp).Row

strMyListLocation = "=" & strTableOfDatesDataSheetName & _
"!R1C" & lngColNum & ":R" & lngMyBot & "C" & lngColNum

RefersToR1C1:=strMyListLocation
.Range(strTableOfDatesColumn & 1).Select
End With

Sheets(strInCellDropDownSheetName).Select
Range(strCellToGetTheDropDownList).Select
With Selection.Validation
.Delete

strListLocation = "=" & strNameOfThisDateList

xlBetween, Formula1:=strListLocation
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select Date!"
.InputMessage = "Pick the DATE you want from the list here!"
.ShowInput = True
.ShowError = False
End With

With Sheets(strInCellDropDownSheetName).Range(strCellToGetTheDropDownList)
.NumberFormat = "[\$-F800]dddd, mmmm dd, yyyy"
.ColumnWidth = 28
.ClearContents
End With
End Sub

barry houdini

MrExcel MVP

How do you want to do this? If you put the first date in A1 you could use:

Edit > Fill > Series > weekday.

...or you could use a formula.

In A2 copied down

=WORKDAY(A1,1)

Note: WORKDAY function is part of Analysis ToolPak addin. An alternative without Analysis ToolPak,

=A1+CHOOSE(WEEKDAY(A1),1,1,1,1,1,3,2)

Joe Was

MrExcel MVP
Note: "Barry's" last formula: "=A1+CHOOSE(WEEKDAY(A1),1,1,1,1,1,3,2)" is a copy Across Columns Formula and Not a copy Down Rows Formula!

Where cell "A1" contains the starting seed date.

And, it works well.

Board Regular
Thank you guys for the help. I tried the =WORKDAY(A1,1)
formula and it worked. couldn't quiet understand the other codes. sorry i am still an amateur in excel.

how do i get the list to stop at the end of the month? what i mean is if i copy the formula =WORKDAY(A1,1) in the whole column how i do get excel to stop on the last day of the month?

hope my question is clear...

thank you again,

barry houdini

MrExcel MVP

You can do that but the formula will be a little more complex....

If you put the 1st day of the month, i.e. 8/1/2007, in A1 [ format as mmm-yy, perhaps, so that it displays as Aug-07]

then in A3 use this formula copied down to A25

=IF(MONTH(WORKDAY(A\$1-1,ROWS(A\$3:A3)))=MONTH(A\$1),WORKDAY(A\$1-1,ROWS(A\$3:A3)),"")

Board Regular
Hey Barry,

You rock man. totally!!! thanks... it worked. i was on the verge of pulling my hair apart.

Replies
20
Views
292
Replies
7
Views
154
Replies
8
Views
71
Replies
2
Views
92
Replies
3
Views
59

1,191,317
Messages
5,985,944
Members
439,991
Latest member
NCWalker

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.

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

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