| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Current year and current week number
I can obtain the current year with YEAR(TODAY()) and the current week number
with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
1) You have an extra $ sign - the formula should read:
=IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
You haven't told us how it fails, but:
B$1, rather than $B$1, so that it copies across appropriately ? -- David Biddulph "Grey Old Man" wrote in message ... I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
I would like to take this opportunity to introduce you to Chip Pearson's page
regarding WEEKNUM. After reading - try both formulas: =TRUNC(((TODAY()-DATE(YEAR(TODAY()),1,0))+6)/7) =WEEKNUM(TODAY()) and see for yourself... http://www.cpearson.com/excel/WeekNumbers.aspx Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
It's the formula logic rather than the cell addressing that is causing the
problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
Be my guest and call it whatever you feel like...
Logic, by itself is not enough as long as we talk "Excelling". I do hope the picture in the link will speak for itself - OR I didn't understood your request. http://img17.imageshack.us/img17/4336/nonamep.png Micky "Grey Old Man" wrote: It's the formula logic rather than the cell addressing that is causing the problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
Your answer shows year 2008 weeks 51 and 51 as "N" "N" which is incorrect. It
should be "Y" for the whole of 2008 (and indeed 2010). The year 2009 should show a combination of "Y" "Y" .. until the last few weeks of "N" "N". My IF (AND( ....,....),"Y","N") logic is flawed. I am OK with obtaining Current_Year and Current_Week. Thanks again. "מיכאל (מיקי) אבידן" wrote: Be my guest and call it whatever you feel like... Logic, by itself is not enough as long as we talk "Excelling". I do hope the picture in the link will speak for itself - OR I didn't understood your request. http://img17.imageshack.us/img17/4336/nonamep.png Micky "Grey Old Man" wrote: It's the formula logic rather than the cell addressing that is causing the problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
Try this in B2 and copy across:
=IF(YEAR(TODAY())$A2,"Y",IF(AND(YEAR(TODAY())=$A 2,WEEKNUM(TODAY())=B$1),"Y","N")) Micky "Grey Old Man" wrote: Your answer shows year 2008 weeks 51 and 51 as "N" "N" which is incorrect. It should be "Y" for the whole of 2008 (and indeed 2010). The year 2009 should show a combination of "Y" "Y" .. until the last few weeks of "N" "N". My IF (AND( ....,....),"Y","N") logic is flawed. I am OK with obtaining Current_Year and Current_Week. Thanks again. "מיכאל (מיקי) אבידן" wrote: Be my guest and call it whatever you feel like... Logic, by itself is not enough as long as we talk "Excelling". I do hope the picture in the link will speak for itself - OR I didn't understood your request. http://img17.imageshack.us/img17/4336/nonamep.png Micky "Grey Old Man" wrote: It's the formula logic rather than the cell addressing that is causing the problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
It's my logic =IF(AND( ....,....),"Y","N") that is flawed. The cell
referencing is OK. "David Biddulph" wrote: You haven't told us how it fails, but: B$1, rather than $B$1, so that it copies across appropriately ? -- David Biddulph "Grey Old Man" wrote in message ... I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
Year 2009 now contains a full row of "N"'s.
How have you formatted the cells in row 1 and column A? "מיכאל (מיקי) אבידן" wrote: Try this in B2 and copy across: =IF(YEAR(TODAY())$A2,"Y",IF(AND(YEAR(TODAY())=$A 2,WEEKNUM(TODAY())=B$1),"Y","N")) Micky "Grey Old Man" wrote: Your answer shows year 2008 weeks 51 and 51 as "N" "N" which is incorrect. It should be "Y" for the whole of 2008 (and indeed 2010). The year 2009 should show a combination of "Y" "Y" .. until the last few weeks of "N" "N". My IF (AND( ....,....),"Y","N") logic is flawed. I am OK with obtaining Current_Year and Current_Week. Thanks again. "מיכאל (מיקי) אבידן" wrote: Be my guest and call it whatever you feel like... Logic, by itself is not enough as long as we talk "Excelling". I do hope the picture in the link will speak for itself - OR I didn't understood your request. http://img17.imageshack.us/img17/4336/nonamep.png Micky "Grey Old Man" wrote: It's the formula logic rather than the cell addressing that is causing the problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
The cell referencing would be OK if, as you copy the formula across, you
still want the formula to refer to cell B1, so that each week's column will be populated as if it were week 1. I'd be surprised if that's what you want. -- David Biddulph "Grey Old Man" wrote in message ... It's my logic =IF(AND( ....,....),"Y","N") that is flawed. The cell referencing is OK. "David Biddulph" wrote: You haven't told us how it fails, but: B$1, rather than $B$1, so that it copies across appropriately ? -- David Biddulph "Grey Old Man" wrote in message ... I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Current year and current week number
This now works.
Thank you. "מיכאל (מיקי) אבידן" wrote: Try this in B2 and copy across: =IF(YEAR(TODAY())$A2,"Y",IF(AND(YEAR(TODAY())=$A 2,WEEKNUM(TODAY())=B$1),"Y","N")) Micky "Grey Old Man" wrote: Your answer shows year 2008 weeks 51 and 51 as "N" "N" which is incorrect. It should be "Y" for the whole of 2008 (and indeed 2010). The year 2009 should show a combination of "Y" "Y" .. until the last few weeks of "N" "N". My IF (AND( ....,....),"Y","N") logic is flawed. I am OK with obtaining Current_Year and Current_Week. Thanks again. "מיכאל (מיקי) אבידן" wrote: Be my guest and call it whatever you feel like... Logic, by itself is not enough as long as we talk "Excelling". I do hope the picture in the link will speak for itself - OR I didn't understood your request. http://img17.imageshack.us/img17/4336/nonamep.png Micky "Grey Old Man" wrote: It's the formula logic rather than the cell addressing that is causing the problem. My attempt does not return the required results. "מיכאל (מיקי) אבידן" wrote: 1) You have an extra $ sign - the formula should read: =IF(AND(YEAR(TODAY())=$A2,WEEKNUM(TODAY())=B$1), "Y","N") 2) I hope you turned on the Analysis Toolpak add-in in order to be able to use the WeekNum function. Micky "Grey Old Man" wrote: I can obtain the current year with YEAR(TODAY()) and the current week number with WEEKNUM, but I want to display the results in an ?array. By this I mean; row 1 contains the week nos 1 -52; column A contains the years 2010,2009,2008. I want the result to look like: 2010 N N N -----N until week 52 2009 Y Y Y ------ Y until current week, then N N N until week 52 2008 Y Y Y ------ Y until week 52 My current attempt fails, which is =IF(AND(Current_Year=$A2,Current_Week=$B$1),"Y", "N") The purpose is to automatically populate a chart (graph) with current data (Y) and ignore future data (N). Thanks in anticipation. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
| Formula to determine number of current records by week | Excel Worksheet Functions | |||
| Show week number in current month | Excel Discussion (Misc queries) | |||
| How to compare current year to prior year in bar chart? | Charts and Charting in Excel | |||
| HELP - need to returns the current number of past month this year and ... | Excel Worksheet Functions | |||
![Grey Old Man[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
