Current year and current week number

[Grey Old Man]

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.

 

[מיכ×ל (מיקי) ×בידן]

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

 

[David Biddulph]

You haven't told us how it fails, but:

B$1, rather than $B$1, so that it copies across appropriately ?

 

[מיכ×ל (מיקי) ×בידן]

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]

It's the formula logic rather than the cell addressing that is causing the
problem. My attempt does not return the required results.

 

[מיכ×ל (מיקי) ×בידן]

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]

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.

 

[מיכ×ל (מיקי) ×בידן]

Try this in B2 and copy across
=IF(YEAR(TODAY())>$A2,"Y",IF(AND(YEAR(TODAY())>=$A2,WEEKNUM(TODAY())>=B$1),"Y","N"))
Micky

 

[Grey Old Man]

It's my logic =IF(AND( ....,....),"Y","N") that is flawed. The cell
referencing is OK.

 

[Grey Old Man]

Year 2009 now contains a full row of "N"'s.
How have you formatted the cells in row 1 and column A?

 

[David Biddulph]

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.

 

[Grey Old Man]

This now works.
Thank you.

Try this in B2 and copy across:
=IF(YEAR(TODAY())>$A2,"Y",IF(AND(YEAR(TODAY())>=$A2,WEEKNUM(TODAY())>=B$1),"Y","N"))
Micky