Convert WeekNum to Sunday's date
 

Hello,
What formula can convert a week number to the Sunday's date for the week
number listed. Example:
Year WeekNumber Formula Result
2006 3 Jan 15,2006
2005 51 Dec 18,2005
I have searched and still can't find the solution.
Thanks you very much for your help!
Terri

Convert WeekNum to Sunday's date
 

If year is in A1 and week number in B1

=DATE(A1,1,1)-WEEKDAY(DATE(A1,1,7))+B1*7


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=526843

Convert WeekNum to Sunday's date
 

Terri --
If Year is Column A, Wk# is Column B, Result is Column C, then this works in
Column D =

=IF(WEEKDAY(C2)1,C2-(WEEKDAY(C2)-1),C2)

It says, "Take the day of the week of the formula result. If it's anything
other than a '1' (the first day of the week), subtract one less than that
number to get back to Sunday of that week. If it already is Sunday, just
leave it alone."

It's kind of brute force, but it works. Maybe someone else can be a bit
more elegant.

HTH

"Terri" wrote:

Hello,
What formula can convert a week number to the Sunday's date for the week
number listed. Example:
Year WeekNumber Formula Result
2006 3 Jan 15,2006
2005 51 Dec 18,2005
I have searched and still can't find the solution.
Thanks you very much for your help!
Terri

Convert WeekNum to Sunday's date
 

Wow!! Works great!
Thank You!

"daddylonglegs" wrote:


If year is in A1 and week number in B1

=DATE(A1,1,1)-WEEKDAY(DATE(A1,1,7))+B1*7


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=526843