| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inverse to WEEKNUM
I know that using the function WEEKNUM you can get the number for a week
through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
Maybe not the prettiest thing, but if you put 1/1/08 in B1 and 40 in A1,
=IF(WEEKDAY(B1+A1*7,1)=2,WEEKDAY(B1+A1*7,1),WEEKDA Y(B1+A1*7,1)-WEEKDAY(B1+A1*7,1)+2) should get you there. "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
try this
=DATEVALUE("Dec 31, 2007")+($B$2-1)*7 -- Don Guillett Microsoft MVP Excel SalesAid Software "Alonso" wrote in message ... I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+7*(A2-1)+2
but note that in some years, such as this, there isn't a Monday in week 1 (as it's actually week 53 of the previous year). -- David Biddulph "Alonso" wrote in message ... I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
=DATE(YEAR(TODAY()),1,1)-8+(A1)*7
"Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
Looks like the best one -- Don Guillett Microsoft MVP Excel SalesAid Software "Teethless mama" wrote in message ... =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
Hi Mama
I am curious to know why you deducted 8 in the formula -- cprao "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
The 8 is OK for 2008, but not for other years.
See other formulae suggested. -- David Biddulph "cprao" wrote in message ... Hi Mama I am curious to know why you deducted 8 in the formula -- cprao "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
as Don says
looks like the best one and above all it worked perfectly thanks!! "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
.... providing that YEAR(TODAY()) is 2008, so in that case you could use
=DATE(2008,1,1)-8+(A1)*7 For other years, you need one of the other formulae suggested (or alter the -8 parameter). -- David Biddulph "Alonso" wrote in message ... as Don says looks like the best one and above all it worked perfectly thanks!! "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
thanks for the remark David
i noticed that your formula is similar I'll keep both, just to track any changes "David Biddulph" wrote: .... providing that YEAR(TODAY()) is 2008, so in that case you could use =DATE(2008,1,1)-8+(A1)*7 For other years, you need one of the other formulae suggested (or alter the -8 parameter). -- David Biddulph "Alonso" wrote in message ... as Don says looks like the best one and above all it worked perfectly thanks!! "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
David
for year 2009 what changes should be made to the formulas either your's or mama's "David Biddulph" wrote: .... providing that YEAR(TODAY()) is 2008, so in that case you could use =DATE(2008,1,1)-8+(A1)*7 For other years, you need one of the other formulae suggested (or alter the -8 parameter). -- David Biddulph "Alonso" wrote in message ... as Don says looks like the best one and above all it worked perfectly thanks!! "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inverse to WEEKNUM
My original reply
will work for 2009, as for other years. -- David Biddulph "Alonso" wrote in message ... David for year 2009 what changes should be made to the formulas either your's or mama's "David Biddulph" wrote: .... providing that YEAR(TODAY()) is 2008, so in that case you could use =DATE(2008,1,1)-8+(A1)*7 For other years, you need one of the other formulae suggested (or alter the -8 parameter). -- David Biddulph "Alonso" wrote in message ... as Don says looks like the best one and above all it worked perfectly thanks!! "Teethless mama" wrote: =DATE(YEAR(TODAY()),1,1)-8+(A1)*7 "Alonso" wrote: I know that using the function WEEKNUM you can get the number for a week through a year eg. =WEEKNUM(09/29/2008) gets 40 my question is can it be done backwards?? what i want to do is type the number of the week (A1) and get the date of the monday of that week (on B1) ie if i type 41 i should get 10/06/2008 (the date of the monday of week 41) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a way to do an inverse two-way lookup? | Excel Discussion (Misc queries) | |||
| TAN INVERSE ? | Excel Worksheet Functions | |||
| Inverse rank | Excel Discussion (Misc queries) | |||
| log inverse | Excel Discussion (Misc queries) | |||
| Inverse sin | Excel Worksheet Functions | |||
![Alonso[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
