Get Actual Date from Day of Week & Week Number

Hello Experts,

The attached VBA formula will give you the week number based on two variables (First day of the year and a given date): (Note that Starting day of the year is hard-coded in the formula)

What I need to do is actually to reverse this formula, i.e. to supply week number and Day of week (Sat, Sun, Mon,...Fri) and I get the corresponding date to that day.

For exaple, if I want to know what the date corresponding to Saturday on week 12, the function call will look like the following:

Dim myDate as Date

myDate = GetDate("Sat", 12)
 
Thank you for your help

Function FiscalWeek(aDate As Date) As Long

Dim yearStart As Date
Dim dayGap As Long, wCount As Long

yearStart = DateSerial(2010, 9, 18)
dayGap = aDate - yearStart

FiscalWeek = Int(dayGap / 7) + 1

End Function

Select allOpen in new window