Reputation: 3653

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 way to go?

Upvotes: 7

Answers (7)

David Reid

Reputation: 1

Sub Weekly_Actuals_Import()

    
    Dim lastFridayDate As Date
    
    lastFridayDate = (Date - ((Weekday(Now)) + 1))
    'Today as "date" minus weekday index number plus one returns last friday's date.
        
    Sheets.Add(after:=ActiveSheet).Name = Format(lastFridayDate, "MM-DD-YYYY")
    'formatted in MM-DD-YYYY to allow entry as a worksheet name, as MM/DD/YYYY is not acceptable
    
End Sub

Upvotes: 0

Michael Seltene

Reputation: 591

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

Upvotes: 0

anandhu

Reputation: 780

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

Upvotes: 0

Alex P

Reputation: 12497

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

Upvotes: 15

Pec1983

Reputation: 376

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)

Upvotes: 0

Nick

Reputation: 3653

DateAdd("d", -1 - Weekday(Now), Now)

Upvotes: 7