dailymeat
asked on
Need to find last vbFriday
Need to find last vbFriday vba
If you mean the last Friday of the month, then this is a solution:
Public Function GetLastFriday(ByVal parmAnyDate As Date) As Date
'returns the date of the last Friday in the same month as parmAnyDate
Dim dtLastDOM As Date
'Initialize to last day of the month
dtLastDOM = DateSerial(Year(parmAnyDate), Month(parmAnyDate) + 1, 0)
'Decrement until Friday
Do Until Weekday(dtLastDOM) = vbFriday
dtLastDOM = dtLastDOM - 1
Loop
GetLastFriday = dtLastDOM
End Function
Here's a no-looping version:
Public Function GetLastFridayNoLoop(ByVal parmAnyDate As Date) As Date
'returns the date of the last Friday in the same month as parmAnyDate
Dim dtLastDOM As Date
Dim lngWeekdayDiff As Long
'Initialize to last day of the month
dtLastDOM = DateSerial(Year(parmAnyDate), Month(parmAnyDate) + 1, 0)
'calculate days difference to Friday
lngWeekdayDiff = Weekday(dtLastDOM) - vbFriday
If Sgn(lngWeekdayDiff) = -1 Then 'adjust if negative
dtLastDOM = dtLastDOM - lngWeekdayDiff - 7
Else
dtLastDOM = dtLastDOM - lngWeekdayDiff
End If
GetLastFridayNoLoop = dtLastDOM
End Function
If you want to find the last Friday of the month, you may also try this...
Function getLastFriday(dt As Date) As Date
getLastFriday = DateSerial(Year(dt), Month(dt) + 1, 1) - Weekday(DateSerial(Year(dt), Month(dt) + 1, 1) - 6)
End Function
Sub Test()
MsgBox getLastFriday(Date)
End Sub
ASKER
Thank you.
Subodh, the function works in every day except on Fridays. If today's date is Friday, it does not show last Friday it shows today's.
Subodh, the function works in every day except on Fridays. If today's date is Friday, it does not show last Friday it shows today's.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you test my solutions
ASKER
Aikimark, I did not because your solution is for the last Friday of the month, thanks anyways for your effort.
Subodh, It worked!, thanks.
Subodh, It worked!, thanks.
You're welcome! Glad it worked as desired.
So, you really needed "Prior or current Friday", not "Last Friday"
Open in new window
Then you can call this function like below...
Open in new window