Get Dates for All Mondays in a Month

[Kesey]

Hello,

I'm working on a project where I request a Month (always the first of the month) and a day of the week from the user. Based on that date and day, I want to write out all of the Mondays (if that's the day entered) of the month to separate cells.

So for example, the user enters 4/1/2010 into cell A1 and Monday into cell A2. I would like a macro to write all of the Mondays in April to cells column B. Once the macro runs, column B should contain 4/5/2010, 4/12/2010, 4/19/2010, and 4/26/2010.

Similarly if the user entered 4/1/2010 and Wednesday, the macro would write 4/7/2010, 4/14/2010, 4/21/2010, and 4/28/2010 to cells in column B.

Any suggestions?

Thanks!

 

[p45cal]

My previous post regarding the macro version has a compatibility problem with XL2003. It was designed in XL2007. To allow it to work in pre xl2007 version too, change the line:

LastOfMonth = CDate(Application.EoMonth(MonthYear, 0))

to:

LastOfMonth = DateSerial(Year(MonthYear), Month(MonthYear) + 1, 0)

 

[Kesey]

p45cal, in reality my cells are more ambiguous than A1 and A2, and I don't know where I need the dates until the macro is run. So I don't want to add any formulas to the worksheet initially.

I tried to adapt what you created by doing the following:

Sub GenerateDates()

    Dim dtStartDate As Date
    Dim dtWeekDates As Date
    Dim strDayOfWeek As String
    Dim i As Integer
    
    dtStartDate = 12 / 1 / 2010
    strDayOfWeek = "Monday"
    
    For i = 1 To 5
    dtWeekDates = NthWeekday(dtStartDate, strDayOfWeek, i)
    MsgBox (dtWeekDates)
    Next i
    

End Sub

Function NthWeekday(MonthYear, DayOfWeek, Nth)
Dim FirstOfMonth As Date
Dim LastOfMonth As Date
Dim WeekDayNum As Long
Dim i As Integer
Dim count As Integer

NthWeekday = "Oops!"
FirstOfMonth = DateSerial(Year(MonthYear), Month(MonthYear), 1)
LastOfMonth = CDate(Application.EoMonth(MonthYear, 0))
WeekDayNum = Application.WorksheetFunction.Match(Left(UCase(DayOfWeek), 2), Array("MO", "TU", "WE", "TH", "FR", "SA", "SU"), 0)
count = 0
For i = FirstOfMonth To LastOfMonth
  If Weekday(i, vbMonday) = WeekDayNum Then
    count = count + 1
    If count = Nth Then
      NthWeekday = i
      Exit For
    End If
  End If
Next i
End Function

It's blowing up on LastOfMonth (Object doesn't support this property or method). Any thoughts?

 

[Kesey]

Guess we were posting at the same time. I am using XL2003 so I corrected that. Now I'm getting an overflow error on this line of code in the function:

For i = FirstOfMonth To LastOfMonth

I changed my code to call the function a bit to this:

Sub GenerateDates()

    Dim dtStartDate As Date
    Dim dtWeekDates As Date
    Dim strDayOfWeek As String
    Dim i As Integer
    
    dtStartDate = Range("C3").Value
    strDayOfWeek = "Monday"
    
    For i = 1 To 5
    dtWeekDates = NthWeekday(dtStartDate, strDayOfWeek, i)
    MsgBox (dtWeekDates)
    Next i
    

End Sub

 

[Kesey]

I guess the overflow error is because I don't have one of the variables defined properly. I took off Option Explicit and un-declared the variables, and that works! Now to figure out where I went wrong.

 

[MrRajKumar]

Hi,

There may be other easiest ways

Firstly enter this formula in A3 or create a range name with the following formula

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&DATE(YEAR(A1),MONTH(A1)+1,0))))=MATCH(LEFT(A2,2),{"Su";"Mo";"Tu";"We";"Th";"Fr";"Sa"},0)))

in A4 enter the below formula

=DATE(YEAR(A1),MONTH(A1)+1,0)

in B1, enter this Array Formula

=IF(COLUMNS($B$1:B1)<=$A$3,INDEX(ROW(INDIRECT($A$1&":"&$A$4)),SMALL(IF(TEXT(ROW(INDIRECT($A$1&":"&$A$4)),"dddd")=$A$2,ROW(INDIRECT($A$1&":"&$A$4))-ROW(INDIRECT($A$1&":"&$A$1))+1),COLUMNS($B$1:B1))),"")

& copy to across..


Raj

 

[p45cal]

It's blowing up on LastOfMonth (Object doesn't support this property or method). Any thoughts?

1. Look at my most recent post a minute before you posted!
2. The combination:

Dim dtStartDate As Date
 dtStartDate = 12 / 1 / 2010

ends up with a dtstartdate value of
#00:08:36#
becaues this is 12 divided by 1 divided by 2010 (0.005970) forced into a date/time type variable.
If you use

dtStartDate = "12 / 1 / 2010"

I suspect the date it returns will be subject to your locale (d/m/y v. m/d/y etc.)
Better to grab a date you're certain of, which ultimately I'd expect to be derived from a worksheet cell somewhere, or use the built in date functions to ensure you get a valid month and year.

..or tweak the function so that it expects those parameters separately.

 

[p45cal]

Hi,

There may be other easiest ways

Raj

There almost certainly are..






..but is your's one of them?!



oh.. is that the time?, better be off then!

 

[Kesey]

p45cal - I think the issue is that sometimes the function will return a date, but sometimes it will return a String ("Oops"). If I don't use Option Explicit on the module where I have your function and do use Option Explicit on the module where I have my Sub, it looks like I'm fine (when defining dtWeekDates as a Variant).

I'm weary of turning off option explicit though. It engrained into my brain that using it is good coding practice.

 

[p45cal]

p45cal - I think the issue is that sometimes the function will return a date, but sometimes it will return a String ("Oops"). If I don't use Option Explicit on the module where I have your function and do use Option Explicit on the module where I have my Sub, it looks like I'm fine (when defining dtWeekDates as a Variant).

I'm weary of turning off option explicit though. It engrained into my brain that using it is good coding practice.

Keep your option explicit everywhere,
in your GenerateDates modue:

Dim dtWeekDates As Variant

and in the function:

Dim i As Date, FirstOfMonth As Date, LastOfMonth As Date, WeekDayNum As Long, Count As Long

Of course, you don't have to use As nnnn in the Dim statements while developing, but it may reduce overhead if you do so for the finished article.

 

[Kesey]

Perfect, thanks so much!