How to determine Date of the week knowing Week Number

Reverse of WEEKNUM

As far as this FAQ goes, did I come up with the answers? ôNo, I got by with a little help from my friendsö û namely SkipVought, anotherhiggins and mintjulep.
(BTW, for the young ones, thatÆs Beatles û March 29, 1967)

As you probably already know, Date and Time on your PC is a constant that is continually changing. That sounds like an oxymoron, but itÆs not. ItÆs a constant because for any given instance it is a hard number that cannot be changed. Since every instance of Date & Time is different from the other because time is fleeting away, it is also a continually changing constant. As such, it makes Date and Time to be a Serial Number; a series of constants describing the passage of time.

So, if Date and Time are just series of numbers, the calculations should be easy. They are, if you are only counting days, or fractions of days. The reason the calcs are not easy is because the system is not decimal based. We have base of 60, 24, 7, 30, 52, etc which don't quite sit well with our decimal minds.

While Excel has some great Date-Time calculations, it falls short in many areas. For example, try figuring out someoneÆs age knowing their birth date.

How do you find the date for the first day of the week knowing only the week number? Excel has a really nice function to calculate the number of the week for a particular date (WEEKNUM), but the reverse is not available.

The three formulas that work well are:

=INT((DATE(YEAR(NOW()),1,1)+(A2*7)-7)/7)*7+1
(credit: anotherhiggins)

=DATE(YEAR(NOW()),1,1)+(A2*7)-(6+WEEKDAY(DATE(YEAR(NOW()),1,1)))
(credit: mintjulep)

=DATE(YEAR(NOW()),1,1)+A2/52*365-WEEKDAY(DATE(YEAR(NOW()),1,1))-6

Where A2 to refers to a Week Number.

For a particular date, you can calculate the "Week Of" date, using two variables below to slide the starting point one way or another.
=INT((TheDate+Var1)/7)*7+Var2

Another variation on the same formula is
=INT((TheDate-Var1)/7)*7+Var2

The formula with +Var1 slides the Week forward depending on the value of Var1. The formula with ûVar1 slides the week backward depending on the value of Var1

In either case,
Var1 determines the day on which the change occurs
0 - change occurs on Saturday
1 - change occurs on Sunday
2 - change occurs on Monday
3 - change occurs on Tuesday
4 - change occurs on Wednesday
5 - change occurs on Thursday
6 - change occurs on Friday
7 - change occurs on the following Saturday
and
Var2 determines the day-of-week returned
0 - Returns Saturday
1 - Returns Sunday
2 - Returns Monday
3 - Returns Tuesday
4 - Returns Wednesday
5 - Returns Thursday
6 - Returns Friday
7 - Returns the following Saturday

Also, to get any day of the week for a specified date you can use
=TheDate+(Var3-TheDate)

Where Var3 determines the day-of-week returned
1 - Returns Sunday
2 - Returns Monday
3 - Returns Tuesday
4 - Returns Wednesday
5 - Returns Thursday
6 - Returns Friday
7 - Returns Saturday