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

Open in new window

MehawitchiAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Try this:
Function GetDate(strDay As String, lngWeekNum As Long) As Date
   Dim dteTemp As Date
   dteTemp = DateSerial(2010, 9, 18) + (lngWeekNum - 1) * 7 - 1
   Do Until StrComp(Format(dteTemp, "ddd"), strDay, vbTextCompare) = 0
      dteTemp = dteTemp + 1
   Loop
   GetDate = dteTemp
End Function

Open in new window

0
 
peter57rCommented:
I assume you have some validation on the day name.

Function GetDate(wk As Integer, wkday As String) As Date

Dim yearStart As Date
yearStart = DateSerial(2010, 9, 18)
yearStart = yearStart + (wk - 1) * 7
yearStart = yearStart + Nz(Switch(wkday = "Sat", 0, wkday = "Sun", 1, wkday = "Mon", 2, wkday = "Tue", 3, wkday = "Wed", 4, wkday = "Thu", 5, wkday = "Fri", 6), 0)
GetDate = yearStart
End Function

?getdate(1,"sun")
19/09/2010
?getdate(12,"sat")
04/12/2010
0
 
MehawitchiAuthor Commented:
Solution by rorya is working 100%
Solution by  peter57r has a small bug:
"Nz" was not recognized by the VBA compiler and the function halted there, but I appreciate the effort
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Rory ArchibaldCommented:
Nz is an Access function and will work there (but not elsewhere).
0
 
MehawitchiAuthor Commented:
I'm using this function in Excel - That's why.

Thanks for clarifying
0
 
peter57rCommented:
True- I was conscious of it but forgot to change the code in the end.  I made the comment about validating the day, because I knew that nz would not be available-  - but anyway, you have a working solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.