SolvedFind Closest Monday of Month Containing Full Work Week to a Date (1 Viewer)

sxschech

Registered User.
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

Super Moderator
Staff member
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?

Last edited:

Isaac

Lifelong Learner
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

Registered User.
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.

Attachments

• Calendar2022Monday.PNG
64.7 KB · Views: 56

Pat Hartman

Super Moderator
Staff member
This sample will give you a start.

plog

Banishment Pending
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

You've got your good things, and you've got mine.
Brute force but works i think.

Code:
``````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

Registered User.
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

Lifelong Learner
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

You've got your good things, and you've got mine.
Code:
``````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

Active member
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

You've got your good things, and you've got mine.
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
Code:
``````Public Function GetLastFullMondayByDate(TheDate as date)as date
GetLastFullMondayByDate = getLastFullMonday(month(theDate),Year(theDate))
end function``````

sxschech

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

Replies
1
Views
439
Replies
7
Views
823
Replies
17
Views
655
Replies
6
Views
464
Replies
9
Views
505