Finding the first Monday of the year

[snipe]

Hey guys,



What's the easiest way to get the first Monday of the year that is before Jan 1. For 2014, it would pull 12/30/13.

So if Jan 1 is not a Monday, go backwards until you hit a Monday?

[RuralGuy]

That's what I would do. Do you need the procedure?

[snipe]

Yes, I googled and found something that didn't work too well.

[RuralGuy]

Just a minute and I'll post it.

[snipe]

iif(weekday(dateserial(year(date()),1,1),vbMonday)>1,dateadd("d",1-weekday(dateserial(year(date()),1,1),vbMonday),dateserial(year(date()),1,1)),dateserial(year(date()),1,1))

See any issues with this? Seems to work for 2013.

I'm curious if you have a much simpler version.

[RuralGuy]

Put this in a Standard Module:

Public Function FirstMonday(MyYear As Integer) As Date
'-- Returns the Monday before the 1st of the year
   If Nz(MyYear, 0) = 0 Then
      '-- Year not realistic
      MyYear = Year(Date)
   End If
   Dim ThisDate As Date
   ThisDate = DateSerial(MyYear, 1, 1)
   Do While Weekday(ThisDate, vbSunday) <> 2
      ThisDate = ThisDate - 1
   Loop
   FirstMonday = ThisDateEnd Function

[RuralGuy]

I tend to use Public Procedures rather than IIF's as they are easier to document and understand years later by someone else. Just my $0.02.

[snipe]

Very good point. I'll start switching over Public Functions then.

Appreciate the help!

[RuralGuy]

Any time. Glad we could help.