Question:
If I have the number of a given week of a year, how can I get the date of the first day of that week? For example, I want the date of Sunday of the 5th week in 1999.
Answer:
There’s not a built-in function that can do all the work for you on this one. However, you can do this:
- The first day of the year is always January 1st. (duh, right?) That will fall on some day of the week, which you can determine by using the Weekday function. Weekday will return you a number from 1 to 7 indicating what day of the week that date is.
- If you want to go to the beginning of the 5th week, you need to add (4 x 7) days to January 1st. The DateAdd function can do this and return you the date that the fifth week of the year starts on.
- Once you have that date, you can simply check the Weekday value of it to determine how many more days until you hit Sunday.
