Last Friday in Each Month (1 Viewer)

[Drand]

I have googled this and cannot find a solution.

Does anyone have a function which returns the date of the last Friday in a month.

Appreciate your assistance.

Thanks

 

[theDBguy]

Do you want the current month? Or, do you want to pass a date to the function?

 

[Drand]

I want to pass a date to the function. I am creating a payments due table and one of the payment frequencies is the last Friday of each month.

 

[theDBguy]

I want to pass a date to the function. I am creating a payments due table and one of the payment frequencies is the last Friday of each month.

Okay, I'm not in front of a computer now, but the process should be easy. Take the date and determine the last day of the month using DateSerial(). Then, using Weekday(), check the day of the week for the last day of the month. You should be able to walk the days back from there.

 

[KitaYama]

Public Function LastFriday(dte As Date) As Date

    Dim dt As Date
    Dim i As Integer
  
    i = DatePart("w", DateSerial(Year(dte), Month(dte), 1), vbSaturday)
    dt = DateSerial(Year(dte), Month(dte), 36 - i)
    If Month(dt) > Month(dte) Then
        dt = DateSerial(Year(dte), Month(dte), 29 - i)
    End If
    LastFriday = dt
  
End Function

You can use it like this :
Msgbox LastFriday("2022/06/23")
Msgbox LastFriday("2022/08/02")

You can pass any date.

 

[arnelgp]

also this one.
you pass the Month number, the Year, and which day (vbWeekDay Enum).

Public Function LastNthDayOfTheMonth( _
                        intMonth As Integer, _
                        intYear As Integer, _
                        intDayOfWeek As VbDayOfWeek) As Date
'
' arnelgp
' 23-December-2017
'
' Translated from:
'
' 101 Ready-to-Use Excel Formulas (c)2014
' Michael Alexander
' Dick Kusleika
'
' Returns the last day in which a particular Day of Week falls
'
' Parameters:
'               intMonth        Integer. the month portion (1-12).
'               intYear         Integer. Year portions (eg: 2017)
'               intDayOfWeek    Integer. Example vbMonday, vbTuesday, etc.
'
' Example:
'               What date does the Last Sunday of January 2017 falls?
'               =LastNthDayOfTheMonth(1, 2017, vbSunday)
'
'               How about the Last Tuesday of March 2016?
'               =LastNthDayOfTheMonth(3, 2016, vbTuesday)
'
' Returns:      Date
'
LastNthDayOfTheMonth = DateSerial(intYear, intMonth + 1, 1) - _
                        Weekday(DateSerial(intYear, intMonth + 1, 8 - intDayOfWeek))
End Function