Get Dates for All Mondays in a Month

Kesey

New Member
Joined
Jul 9, 2006
Messages
36
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Macro may not be needed, but I'll have a look tomorrow, in the meantime, these in cells B1:B5:
Code:
=DATE(YEAR($A$1),MONTH($A$1),1+(([B][COLOR=Red]1[/COLOR][/B]-(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)>=WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))*7)+(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))
=DATE(YEAR($A$1),MONTH($A$1),1+(([B][COLOR=Red]2[/COLOR][/B]-(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)>=WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))*7)+(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))
=DATE(YEAR($A$1),MONTH($A$1),1+(([B][COLOR=Red]3[/COLOR][/B]-(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)>=WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))*7)+(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))
=DATE(YEAR($A$1),MONTH($A$1),1+(([COLOR=Red][B]4[/B][/COLOR]-(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)>=WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))*7)+(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))
=DATE(YEAR($A$1),MONTH($A$1),1+(([B][COLOR=Red]5[/COLOR][/B]-(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)>=WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))*7)+(MATCH(LEFT($A$2,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),1))))
There's only one character difference in each.
In some months the 5th row will encroach into the following month.
It uses only the first 3 characters of A2, so long and short day names will work.
 

mthompso

Board Regular
Joined
Apr 18, 2008
Messages
123
You could write a macro that uses a "select case" statement. Then you could have the macro cycle though each of the days in the month and use the WEEKDAY function on each date; if the the WEEKDAY is the desired result, then you can have the macro write the date to the next empty cell in the column.
 

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
i think possibly
with date in a1
day of week in a2
in b1
Code:
=A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)-LOOKUP(A2,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},{0,1,2,3,4,5,6}),3)
in b2 dragged down
Code:
=IF(ISTEXT(B1),"",IF(MONTH(B1+7)<>MONTH($A$1),"",B1+7))
 
Last edited:

Kesey

New Member
Joined
Jul 9, 2006
Messages
36
Thanks all for the quick responses. p45cal and martindwilson your solutions work great. I'm leaning towards yours Martin as that appears not to add a 5th day when crossing months. Although, I could ignore the 5th day when the month doesn't equal the month I'm looking for...hmmm...

Now I have to figure out how to fit this into my macro. I simplified what I was trying to do for this question just to get some direction. This helps a ton.

Thanks again.
 

Kesey

New Member
Joined
Jul 9, 2006
Messages
36
Actually, I posted too soon.

Martin, your solution works great for Monday, but produced incorrect results for Tuesday, Wednesday, Saturday, and Sunday. #N/A for Friday.

p45cal, yours seems flawless.

Thanks!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
=A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)-LOOKUP(A2,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},{0,1,2,3,4,5,6}),3)
For that to work properly the lookup_vector *must* be sorted in ascending order:

LOOKUP(A2,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday"},{6,2,7,1,5,3,4})

the user enters 4/1/2010 into cell A1...(always the first of the month)... and Monday into cell A2.
Try this...

B2:

=A1+7-WEEKDAY(A1-SEARCH(LEFT(A2,2),"xSuMoTuWeThFrSa")/2)

B3 copied down to B6:

=IF(MONTH(B$2+ROWS(B$3:B3)*7)>MONTH(A$1),"",B2+7)

No error checking!

Format as Date
 

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
correct an oversight on my part grr(well it was 2 am here)
but
Code:
LOOKUP(A2,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday"},{6,2,7,1,5,3,4})
needs to be
Code:
LOOKUP(A2,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday"},{4,0,5,6,3,1,2})
Code:
=A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)-LOOKUP(A2,{"Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday"},{4,0,5,6,3,1,2}),3)
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
OK, the macro version as promised.

In the worksheet use as follows:
Code:
=NthWeekday($A$1,$A$2,1)
more generally:

=NthWeekday(MonthYear,DayOfWeek,Nth)

MonthYear any date containing month and year information. Day of month is unimportant; it can be any day.

DayOfWeek a string containing the weekday in English, can be as short as a two-character abbreviation, to the full length of the weekday name. Only the first two characters are used. Upper or lower case, doesn't matter.

Nth, a whole number from 1 upwards, eg. 3 being the 3rd nnnday of the month.


Supported by this in a standard code module:
Code:
Function NthWeekday(MonthYear, DayOfWeek, Nth)
NthWeekday = "Oops!/Empty String/hyphen..  whatever"
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
While testing, it showed up a glitch #NAME?'s formulae when looking for the 5th weekday in December (eg, 5th Tuesday, December 2010).
 

Kesey

New Member
Joined
Jul 9, 2006
Messages
36
Looks like you're close #NAME?. When I enter 12/1/2010 as my date and Monday or Tuesday, dates in January (1/3/2011 and 1/4/2011) are returned for the fifth day so I'd still need the error checking--which I don't mind.

p45cal - Thanks! I'll check it out.
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top