Is there a quick way to generate a weekday calendar?

[excelos]

Hello!

Is there a quick way to generate a list of the weekday dates between a starting and an ending date? Without weekends?

Thanks!

 

[My Aswer Is This]

Here is a Vba solution

Will put dates in Column A of active sheet:

Sub WeekDays_Only()
'Modified  10/5/2018  9:27:42 PM  EDT
Dim i As Long
Dim aa As Long
Dim x As Long
Dim n As Integer
Dim StartDate As Date
Dim StopDate As Date
On Error GoTo M
StartDate = InputBox("Enter Start Date", "Enter Like this   12/18/2018")
StopDate = InputBox("Enter Stop Date", "Enter Like this   12/18/2018")
n = DateDiff("d", StartDate, StopDate)

    For i = 1 To n
        ans = DateAdd("d", i - 1, StartDate)
        aa = Weekday(ans)
            If aa <> 7 And aa <> 1 Then
            x = x + 1
            Cells(x, 1).Value = ans
            End If
    Next
    
    With Columns(1)
        .NumberFormat = "DDDD,MMMM,DD,YYYY"
        .Font.Size = 16
        .AutoFit
        .HorizontalAlignment = xlLeft
    End With
    Exit Sub
M:
    MsgBox "You enter a improper date"
    End Sub

 

[Peter_SSs]

Hello!

Is there a quick way to generate a list of the weekday dates between a starting and an ending date? Without weekends?

Thanks!

Yes. Here are 3 methods.

Manual
1. Enter the first date & select that cell
2. Home ribbon tab -> Fill (in Editing group) -> Series... -> Columns (or Rows if you want the dates across rather than down) -> Date -> Weekday -> Step value 1 -> Stop value: enter the last date -> OK

Formula
Another fairly easy way is by formula like this, copied down.

Excel Workbook
	A	B	C
1	Start	27/09/2018	27/09/2018
2	End	18/10/2018	28/09/2018
3			1/10/2018
4			2/10/2018
5			3/10/2018
6			4/10/2018
7			5/10/2018
8			8/10/2018
9			9/10/2018
10			10/10/2018
11			11/10/2018
12			12/10/2018
13			15/10/2018
14			16/10/2018
15			17/10/2018
16			18/10/2018
Weekdays

Macro
For start and end dates in B1:B2 as above & results in column C

Sub ListWeekdays()
  Dim i As Long
  Dim dStart As Date, dEnd As Date
  
  dStart = Range("B1").Value - 1
  dEnd = Range("B2").Value
  Do
    i = i + 1
    Range("C" & i).Value = WorksheetFunction.WorkDay(dStart, i)
  Loop Until WorksheetFunction.WorkDay(dStart, i + 1) > dEnd
End Sub

 

[Dave Patton]

Excel 2010
	A	B	C
1	Start	Thu 27-Sep-18	Thu 27-Sep-18
2	End	Thu 18-Oct-18	Fri 28-Sep-18
1a

Cell Formulas
Range	Formula
C1	=WORKDAY.INTL(B1-1,1,1)
C2	=IF((C1+1)<=$B$2,WORKDAY.INTL(C1,1,1),"")

Copy C2 down

 

[Peter_SSs]

Copy C2 down

Dave
Although our formula approaches are quite similar, unless you know just how far to copy your C2 down, it results in a series of error values below the required dates. Hence the check in my formula to return "" if the dates are complete.

Also, if the End Date in B2 happens to be a weekend day, your formula produces a date outside the given date range.