CODEWITHSUNDEEP
vbaexceldateweekday
sigil
sigil

Reputation: 9546

Get dates of Mon-Fri of previous week

I'm trying to implement this SO answer to get the dates for Monday-Friday of the previous week, testing with this:

Public Sub listPreviousWeekDays()

Dim dayCtr As Long

For dayCtr = 2 To 6
    Debug.Print DateAdd("ww", -1, Now - (Weekday(Now, dayCtr) - 1))
Next

End Sub

However, this gives:

4/10/2017 9:27:18 AM 
4/11/2017 9:27:18 AM 
4/12/2017 9:27:18 AM 
4/6/2017 9:27:18 AM 
4/7/2017 9:27:18 AM

My goal is for it to return:

4/10/2017 9:27:18 AM 
4/11/2017 9:27:18 AM 
4/12/2017 9:27:18 AM 
4/13/2017 9:27:18 AM 
4/14/2017 9:27:18 AM

EDIT: to avoid an XY problem, I'll explain what I'm trying to do: I want to run a macro that fills in a block of cells with the previous week's Mon-Fri dates.

Upvotes: 1

Views: 2858

Answers (2)

Praveen DA
Praveen DA

Reputation: 354

This should fix the issue.

Public Sub listPreviousWeekDays()

Dim dayCtr As Long
For dayCtr = 2 To 6
Debug.Print DateAdd("ww", -1, Now - (Weekday(Now, vbUseSystemDayOfWeek)) + dayCtr)
Next

End Sub

Upvotes: 1

cxw
cxw

Reputation: 17041

Updated

As the OP pointed out, the linked code doesn't work for this particular use case when the starting date is a Sunday. Here's a fix:

Option Explicit   ' always always always use this

Public Function MondayLastWeek(pdat As Date) As Date
    Dim weekday_increment As Long
    weekday_increment = weekday(pdat, vbMonday) - 1
    MondayLastWeek = DateAdd("ww", IIf(weekday_increment = 6, 0, -1), pdat - weekday_increment)
    ' IIf: if we're on Sunday, pdat-weekday_increment is already the date we want.
End Function

Public Sub listPreviousWeekDays()
    Dim monday As Date
    monday = MondayLastWeek(Now)

    Dim dayCtr As Long
    For dayCtr = 0 To 4
        Debug.Print monday + dayCtr
    Next
End Sub

Original answer

This works for me:

Public Sub listPreviousWeekDays()

    ' First, get last week's Monday just as in the linked question    
    Dim LastMonday As Date
    LastMonday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 1))

    ' Then, loop over the rest of the week.
    ' Monday + 0 days => Monday
    ' Monday + 4 days => Friday
    Dim dayCtr As Long
    For dayCtr = 0 To 4
        Debug.Print LastMonday + dayCtr
    Next

End Sub

Output:

4/10/2017 12:38:17 PM 
4/11/2017 12:38:17 PM 
4/12/2017 12:38:17 PM 
4/13/2017 12:38:17 PM 
4/14/2017 12:38:17 PM

I think the issue is that the second parameter to Weekday doesn't specify which day you want. Instead, it specifies which day of the week is weekday number 1 (some documentation). Instead of messing with Weekday, just get the Monday using the linked answer, then add days as necessary.

Upvotes: 1

Related Questions