Calculating the friday given a specific Week and Year in Excel

Hi,

In cell A1, I have a year and cell A2, I have a week number. Lets say A1 contains 2008 and A2 contains week #3, how do I get the Friday date of the third week of the year 20058 I don't need any VBA code snippets. I just need the formula. In this case, the answer for this would be 01/18/2008 because its the third friday of the year.

Note, I don't want to add any add-on packs or anything. Simple functions to get me what I want.
piyushdabombAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
=DATE(A1, 1, 1) + (6 - WEEKDAY(DATE(A1, 1, 1))) + (A2-(6 >= WEEKDAY(DATE(A1, 1, 1)))) * 7

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
piyushdabombAuthor Commented:
Zorvek,

I'm getting 39444 as shown in the attachment. Can't I change the format to get it to a date? What am I doing wrong here?
Formula.JPG
zorvek (Kevin Jones)ConsultantCommented:
Select the cell, choose the menu command Format->Cells, navigate to the Number tab, and select Date in the left list.

Also, the number 3 goes in cell A2, not B1.

Kevin
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

piyushdabombAuthor Commented:
Nice, it works. Is there a format function I can add to this rather than right click and formatting?
zorvek (Kevin Jones)ConsultantCommented:
No. Formatting can only be done manually with the menu command, or with VBA. However, if you want, you can use this formula and, as long as the cell remains in General format, it will default to a date format:

=DATE(YEAR(DATE(A1,1,1)+(6-WEEKDAY(DATE(A1,1,1)))+(A2-(6>=WEEKDAY(DATE(A1,1,1))))*7),MONTH(DATE(A1,1,1)+(6-WEEKDAY(DATE(A1,1,1)))+(A2-(6>=WEEKDAY(DATE(A1,1,1))))*7),DAY(DATE(A1,1,1)+(6-WEEKDAY(DATE(A1,1,1)))+(A2-(6>=WEEKDAY(DATE(A1,1,1))))*7))

Kevin
piyushdabombAuthor Commented:
How the heck did you figure that out! WOW.
piyushdabombAuthor Commented:
Perfect Solutions!
zorvek (Kevin Jones)ConsultantCommented:
Actually, it's the same formula repeated three times to input each of the three parameters to the DATE function:

   =DATE(YEAR(formula), MONTH(formula), DAY(formula))

If the DATE formula is used alone in a function then Excel treats the result as a date.

Kevin
piyushdabombAuthor Commented:
Zorvek, is EE your full time job? You seem like a ridiculously stellar Excel genius. Considered your own macro development contract agency?
zorvek (Kevin Jones)ConsultantCommented:
>is EE your full time job?
Seems like it. I actually don't spend that much time "working" EE questions. Most of the questions I choose to answer require only a few minutes of my time. When I stop learning new tricks and techniques by participating is probably when I'll "retire" from EE.

>Considered your own macro development contract agency?
Frequently. The problem is that I already have plenty of paying work so my time is limited.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.