neat way to get last friday's date
no matter what day I call the function on.
I know I could write a select case weekday(now) statement, was just wondering if there was a neater 开发者_如何学Cway to go?
Does this help get you started? I just gave it a quick test and seemed to work ok.
Private Sub LastFriday()
Dim iWeekday As Integer, LastFridayDate As Date
iWeekday = Weekday(Now(), vbFriday)
LastFridayDate = Format(Now - (iWeekday - 1), "dd-mmm-yy")
End Sub
DateAdd("d", -1 - Weekday(Now), Now)
DatePart('dddd', now)
or
DatePart('dddd', #1/1/2010#)
...with an explicit date.
This function will find every Friday for the last Month, You can change it to suit other days such as Monday "oldDay = 2" etc the method will work from today's date, but you can change that to suit
Dim todaysDate As Date = Date.Today Dim oldDay As Integer Dim thisWeek As Date
Dim firstWeek As Date
Dim secondWeek As Date
Dim thirdWeek As Date
Dim fourthWeek As Date
''finds the Friday of the end of the current week
''No mattter what day you are working
Dim daycount As Integer
oldDay = Weekday(todaysDate)
thisWeek = todaysDate
If oldDay < 6 Then
daycount = 6 - oldDay
thisWeek = thisWeek.AddDays(+daycount)
ElseIf oldDay > 6 Then
daycount = oldDay - 6
thisWeek = thisWeek.AddDays(-daycount)
End If
firstWeek = thisWeek
secondWeek = thisWeek
thirdWeek = thisWeek
fourthWeek = thisWeek
fourthWeek = firstWeek.AddDays(-28)
thirdWeek = thirdWeek.AddDays(-21)
secondWeek = secondWeek.AddDays(-14)
firstWeek = firstWeek.AddDays(-7)
You can use this easy (But Lazy) method:
Suppose you need to get the previous wednesday, keep on substracting 1 from todays date till you reach wednesday
Dim lastWednesday As DateTime = DateTime.Now.AddDays(-1)
While (lastWednesday.DayOfWeek <> DayOfWeek.Wednesday)
lastWednesday = lastWednesday.AddDays(-1)
End While
MsgBox(lastWednesday )
*This is VB.NET, you can implement the same in VBA
Please see below for two functions, the function getLastFridayDate
gets the last weeks Friday's date whereas the getFriday
gets the nth
Friday's date.
The function getLastFridayDate
requires a date argument. You can use getLastFridayDate(Now)
to get the last Friday's date. Please see below for examples.
Debug.Print getLastFridayDate("25/08/2022") '=> 19/08/2022
Debug.Print getLastFridayDate("27/08/2022") '=> 26/08/2022
The getFriday
function, the nth
parameter is to be supplied with a week number, where 1 is four weeks ago, and 4 is last week. Let say, if today is the 27/08/2022
, then the below would be the output:-
Debug.Print "1 " & getFriday(1) '=> 1 05/08/2022
Debug.Print "2 " & getFriday(2) '=> 2 12/08/2022
Debug.Print "3 " & getFriday(3) '=> 3 19/08/2022
Debug.Print "4 " & getFriday(4) '=> 4 26/08/2022
Function, you can place this code in the Module file so other classes can use these globally.
Function getLastFridayDate(date_ As Date) As Date
Dim lFriday As Date
lFriday = DateAdd("ww", -1, date_ - (Weekday(date_, vbSaturday) - 7))
getLastFridayDate = lFriday
End Function
Function getFriday(nFriday As Integer) As Date
Dim dateNthFriday As Date
dateNthFriday = Now
If nFriday < 1 Or nFriday > 4 Then
Err.Raise 9999, , "Parameter supplied should be between 1 and 4. The value " & nFriday & " is not valid."
End If
arr = Array(4, 3, 2, 1)
For i = arr(nFriday - 1) To 1 Step -1
If i > 1 Then
dateNthFriday = getLastFridayDate(dateNthFriday)
End If
Next i
getFriday = getLastFridayDate(dateNthFriday)
End Function
I hope you find these useful
精彩评论