CODEWITHSUNDEEP
excelexcel-2010vba
warsong
warsong

Reputation: 1038

Excel VBA - Is there a way to get the date in the current week, given only the day name?

I need to work out a date within the current week (e.g. 2017/04/01) given only the day part of the date, is this possible with Excel VBA? I have looked at questions like this excel vba - How to get the Day Name of Date? but they answer a different question. I've also looked at using constants like vbTuesday but they are't helping as I still need to work out how to connect the day name to the current week.

Upvotes: 0

Views: 881

Answers (2)

Ambie
Ambie

Reputation: 4977

A lot depends on whether you want to use vbUseSystemDayOfWeek or if simply treating Monday as the 1st day is acceptable. You'd also need additional code if you wanted to accept, for example, "Mon" and "Monday" and if you wanted to protect the the lookup with UCase.

In principle, though, the function could be as simple as:

Public Function DateFromDayName(dayName As String) As Date
    Dim d As Long

    d = WorksheetFunction.Match(dayName, _
                                Array("Monday", _
                                      "Tuesday", _
                                      "Wednesday", _
                                      "Thursday", _
                                      "Friday", _
                                      "Saturday", _
                                      "Sunday"), _
                                0)

    DateFromDayName = Now - Weekday(Now, vbMonday) + d

End Function

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

The main problem is turning your day name into a day number - so Tue or Tuesday returns day 1 (Monday being day 0).
On first thought I'd use a Select Case statement in VBA, but there may be a better way.

The code below will calculate Mondays date (dReturnDate) and then add a number of days depending on what you ask for. A nonsensical day name will return a #Num error.

Public Function DateFromDay(DayName As String) As Variant

    Dim dReturnDate As Date
    Dim lDayNumber As Long

    dReturnDate = Date - Weekday(Date - 2)

    Select Case DayName
        Case "Mon", "Monday"
            lDayNumber = 0
        Case "Tue", "Tuesday"
            lDayNumber = 1
        Case "Wed", "Wednesday"
            lDayNumber = 2
        Case "Thu", "Thursday"
            lDayNumber = 3
        Case "Fri", "Friday"
            lDayNumber = 4
        Case "Sat", "Saturday"
            lDayNumber = 5
        Case "Sun", "Sunday"
            lDayNumber = 6
        Case Else
            lDayNumber = -1
    End Select

    If lDayNumber >= 0 Then
        DateFromDay = dReturnDate + lDayNumber
    Else
        DateFromDay = CVErr(xlErrNum)
    End If

End Function

On a worksheet:
=DateFromDay("Tuesday") returns 18th April 2017
=DateFromDay("Any Day") returns #Num!

Upvotes: 1

Related Questions