CODEWITHSUNDEEP
vbaexcel
Brendan Gooden
Brendan Gooden

Reputation: 1561

Find date, given WEEKNUM and WEEKDAY - VBA

I'm trying to find a way of finding a date value, given the WEEKNUM and WEEKDAY

For example

WEEKNUM = 9
WEEKDAY = 4   '(Wednesday)

I can use the below function to find the WEEKNUM from the date, but how can I go the other way around?

Function WEEKNUM(D As Date, FW As Integer) As Integer
     WEEKNUM = CInt(Format(D, "ww", FW))
End Function

Upvotes: 0

Views: 386

Answers (1)

Colin Mason
Colin Mason

Reputation: 70

You could use a table on a hidden sheet, and just used Vlookup to find the correct date. EG

DATE          WEEKDAY     WEEK NO    WEEKDAY + WEEK NO
01/03/2016    Tuesday     Week 10    Tuesday 10
02/03/2016    Wednesday   Week 10    Wednesday 10

In VBA, you could then look up the name of the sheet, which is the weekday, and got the week number from the filename of the workbook, then use vlookup to find the correct date.

weekNum = Mid(ThisWorkbook.Name, 6, 2)
searchStr = ActiveSheet.Name & " " & weekNum & " " & Format(Now(), "YYYY")
rowNo = Application.WorksheetFunction.Match(searchStr, Sheets("WEEKLIST").Range("D:D"), 0)

strSubject = Format(Sheets("WEEKLIST").Range("A" & rowNo).Value, "DD/MM")

Upvotes: 1

Related Questions