Solved Find Closest Monday of Month Containing Full Work Week to a Date

[sxschech]

Probably making this more complex than need be.

Can this be done in code/formula to do one the following without having to create a table of dates. Different statements trying to reach the same result (whichever is easier).

Option 1: Find the 4th Monday of the Month, EXCEPTION - if the workweek of that Monday does not end on Friday, use the previous Monday (Feb, March, May are examples)
Option 2: Find the Monday Date on or before the 24th of the month, EXCEPTION where on or after 24 contains a full work week ending on Friday (April, July, September are examples)
Option 3: Find the last Monday of the month where there is a full work week ending on Friday

 

[jdraw]

What exactly would constitute less than a full work week?
Are you implying a statutory holiday falls within the week?
Please provide a list of Holidays.
Work week is Mon-Fri, right?

 

[Isaac]

if the workweek of that Monday does not end on Friday

What does this mean? The workweek of any monday always falls on a friday, unless you're a workahalic.

May is an example? the 4th monday has a friday happily coming up right after it, and still in May..the 27th

 

[sxschech]

Looks like my explanation left more questions than answers.
Simply looking at work week Monday - Friday
Not considering Holidays
Just trying to get the 4th Monday that is a full week of days Monday Tuesday Wed, Thur and Fri.
If the week ending on the 4th Monday does not include a Friday, then use the previous week's Monday
I did a screen shot for 2022 and yellow highlighted the Mondays if that is of use.

 

[Pat Hartman]

This sample will give you a start.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[plog]

First, I think I have a concise definition of what you want which will be helpful in following my logic in getting what you want:

The date of the Monday before the last Friday of a month.

I suggest you build a custom function that you pass a date and it returns the date you want. It's signature would look like this:


Function get_LastMonday(in_Date As Date) As Date

Inside it you would determine the last day of the month in_Date occurs in. That involves constructing the first day of the next month (DateAdd(), Month(), Year(), CDate()) and then subtracting 1 (DateAdd())

Next you determine the day of the week for that last day (Weekday()).

Then you subtract the correct number of days from the end of the month to get the date of the Monday you want (DateAdd())

For example, if the last day of the month is Friday subtract 4, if Saturday subtract 5..,if Thursday subtract 10 (I may be off by one, but the logic is sound).

 

[MajP]

Brute force but works i think.

Public Function GetLastFullMonday(TheMonth As Integer, TheYear As Integer)
As Date
Dim lastDay As Date
Dim i As Integer
lastDay = DateSerial(TheYear, TheMonth + 1, 0)
Select Case Weekday(lastDay)
Case vbMonday
GetLastFullMonday = lastDay - 7
Case vbTuesday
GetLastFullMonday = lastDay - 8
Case vbWednesday
GetLastFullMonday = lastDay - 9
Case vbThursday
GetLastFullMonday = lastDay - 10
Case vbFriday
GetLastFullMonday = lastDay - 4
Case vbSaturday
GetLastFullMonday = lastDay - 5
Case vbSunday
GetLastFullMonday = lastDay - 6

End Select
End Function
Public Sub testit()
Debug.Print GetLastFullMonday(9, 2022)
End Sub

 

[sxschech]

Thanks MajP. I ran through the dates for 2022 and 2023 and is providing the answer was seeking. Have added it to the appropriate code module.
Thanks plog, looks like MajP provided the actual code that you were suggesting.
Thanks Pat, I downloaded your file to save for future use when dealing with date issues and will try it out.

 

[Isaac]

First, I think I have a concise definition of what you want which will be helpful in following my logic in getting what you want:

The date of the Monday before the last Friday of a month.

Kudos for figuring that one out, I would have never arrived at that in a million years from the description!

 

[MajP]

Public Function GetLastFullMonday(TheMonth As Integer, TheYear As Integer)
As Date
  Dim lastDay As Date
  Dim wkd As Integer
  lastDay = DateSerial(TheYear, TheMonth + 1, 0)
  wkd = Weekday(lastDay)
  If wkd < 6 Then
      GetLastFullMonday = lastDay - (wkd + 5)
  Else
      GetLastFullMonday = lastDay - (wkd - 2)
  End If
End Function

A little more concise code.

 

[Mike Krailo]

That was similar to the way I was thinking it could be done but couldn't get the code quite that short. Well done on that last solution MajP. You could also do away with the TheMonth and TheYear args in favor of a simple date that would ignore the day part of the date. That way, the date function could be used as the input to GetLastFullMonday.

 

[MajP]

That way, the date function could be used as the input to GetLastFullMonday.

A wrapper function takes one function and wraps it in another. Unfortunately in VBA you cannot Overload a function like in vb.net

Function Overloading

Learn more about: Function Overloading

docs.microsoft.com

Public Function GetLastFullMondayByDate(TheDate as date)as date
  GetLastFullMondayByDate = getLastFullMonday(month(theDate),Year(theDate))
end function

 

[sxschech]

Thanks for the update. Always appreciate your great set of code insights and improvements.