Calculate Following Friday Based on a Week Number

[laurag86]

I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for “week 1” is 1/13/12.
<o>(If it helps this is for payroll information and I'm trying to calculate the pay date based of of data from that week)</o>
Thanks for your help!

 

[taurean]

What exactly does following friday mean in this case?
As WEEKNUM function returns 2 (2nd week) for 1/13/12.

 

[BenMiller]

So your "week numbers" are arbitrary, then? Week 1 in 2012 would be 1/6/2012 for Friday.

 

[laurag86]

Yes, I have the follow data in columns a-c:

<TABLE style="WIDTH: 161pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=215><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 50pt; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" id=td_post_2296232 class=xl76 height=20 width=67>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 27pt; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72 width=36>Sat</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 84pt; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73 width=112>31-Dec</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" id=td_post_2296232 class=xl72>Sun</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl77>1-Jan-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Mon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>2-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Tue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>3-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Wed</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>4-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Thu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>5-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 8pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72>Fri</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #215967; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl73>6-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Sat</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>7-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Sun</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>8-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Mon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>9-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Tue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>10-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Wed</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Thu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>12-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Fri</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>13-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 height=20>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Sat</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>14-Jan</TD></TR></TBODY></TABLE>

I want to beable to select "week 1" from my drop down box and it to put in a different cell "1/13/11"

If I choose "week 2" it will pull in that other cell "1/20/11"

 

[BenMiller]

Is that a typo? The Friday of your "Week 1" is the 6th, and "Week 2" is the 13th" ...?

 

[laurag86]

No I want to use the actual data, posted above, and it to pull the follow friday after that week.

I want to use:

Days worked "12/31/11-1/6/12" (or week 1) and it to pull pay date 1/13/12
Or Days worked "1/7/12-1/13/12" (week 2) and it to pull pay date 1/20/12
and so forth.

 

[taurean]

Supposing that Week number is in Column A, this seems to work:
=DATE(2012,1,1)+(A2)*7+5

 

[laurag86]

Beautiful! Thank you so much!

 

[taurean]

That approach will be valid only for 2012. A more general formula that can be used in any year would be:
=DATE(2012,1,1)+LOOKUP(WEEKDAY(DATE(2012,1,1)),{1,2,3,4,5,6,7},{5,4,3,2,1,0,6})+A2*7