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]

Macro may not be needed, but I'll have a look tomorrow, in the meantime, these in cells B1:B5:

=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]

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]

i think possibly
with date in a1
day of week in a2
in b1

=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

=IF(ISTEXT(B1),"",IF(MONTH(B1+7)<>MONTH($A$1),"",B1+7))

 

[Kesey]

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]

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]

=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]

correct an oversight on my part grr(well it was 2 am here)
but

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

needs to be

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

=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]

OK, the macro version as promised.

In the worksheet use as follows:

=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:

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]

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.