• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

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

0
Mehawitchi
Asked:
Mehawitchi
  • 2
  • 2
  • 2
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now