Current year and current week number

G

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
 
D

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
 
G

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.
 
G

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
 
G

Grey Old Man

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

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?
 
D

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.
 
G

Grey Old Man

This now works.
Thank you.

מיכ×ל (מיקי) ×בידן said:
Try this in B2 and copy across:
=IF(YEAR(TODAY())>$A2,"Y",IF(AND(YEAR(TODAY())>=$A2,WEEKNUM(TODAY())>=B$1),"Y","N"))
Micky
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top