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

sxschech

Registered User.
Local time
Yesterday, 22:00
Joined
Mar 2, 2010
Messages
754
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
Local time
Today, 01:00
Joined
Jan 23, 2006
Messages
14,439
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
Local time
Yesterday, 22:00
Joined
Mar 14, 2017
Messages
6,680
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.
Local time
Yesterday, 22:00
Joined
Mar 2, 2010
Messages
754
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
    Calendar2022Monday.PNG
    64.7 KB · Views: 56

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:00
Joined
Feb 19, 2002
Messages
36,961
This sample will give you a start.

 

plog

Banishment Pending
Local time
Today, 00:00
Joined
May 11, 2011
Messages
11,020
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.
Local time
Today, 01:00
Joined
May 21, 2018
Messages
6,444
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.
Local time
Yesterday, 22:00
Joined
Mar 2, 2010
Messages
754
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
Local time
Yesterday, 22:00
Joined
Mar 14, 2017
Messages
6,680
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.
Local time
Today, 01:00
Joined
May 21, 2018
Messages
6,444
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
Local time
Today, 01:00
Joined
Mar 28, 2020
Messages
635
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.
Local time
Today, 01:00
Joined
May 21, 2018
Messages
6,444
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.
Local time
Yesterday, 22:00
Joined
Mar 2, 2010
Messages
754
Thanks for the update. Always appreciate your great set of code insights and improvements.
 

Users who are viewing this thread

Top Bottom