piyushdabomb
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Also, the number 3 goes in cell A2, not B1.
Kevin
ASKER
Nice, it works. Is there a format function I can add to this rather than right click and formatting?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How the heck did you figure that out! WOW.
ASKER
Perfect Solutions!
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
=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
ASKER
Zorvek, is EE your full time job? You seem like a ridiculously stellar Excel genius. Considered your own macro development contract agency?
>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.
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.
ASKER
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