| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert a list of date of births into age in Excel? | Excel Worksheet Functions | |||
| How do I convert a julian date to a regular date? | Excel Worksheet Functions | |||
| Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
| How do I convert a text string into a date? | Excel Worksheet Functions | |||
| Date format not correct when you convert a CSV text file in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
