Weeknum and an Array

[Guest]

My issue with WEEKNUM( ) is this:

I'm trying to input the formula over multiple cells ... I do it all the time with other formulas but for some reason this function does not want to take an array as an operand. WEEKNUM(A1:A5,1) will give me a #VALUE error in all the cells.

Curiously, I'm able to other Date Formulas, such as MONTH( ), with no problem, so I don't necessarily think its a formatting issue ... Please Help!

 

[Harlan Grove]

My issue with WEEKNUM( ) is this:

I'm trying to input the formula over multiple cells ... I do it all the time
with other formulas but for some reason this function does not want to take
an array as an operand. WEEKNUM(A1:A5,1) will give me a #VALUE error in all
the cells.

Curiously, I'm able to other Date Formulas, such as MONTH( ), with no problem,
so I don't necessarily think its a formatting issue ... Please Help!

Plain & simple, whoever programmed WEEKNUM screwed it up so that it only accepts
scalar arguments, meaning single values only. WEEKNUM is part of the Analysis
ToolPak, so it's possible that its programmer never worked on core Excel. Most
of the other date functions are in core Excel.

So, what to do? One work-around for WEEKNUM(A1:A5,1) would be the array formula

=CEILING((A1:A5-DATE(YEAR(A1:A5),1,1)+WEEKDAY(DATE(YEAR(A1:A5),1,1), 1 ))/7,1)

where the 1 that I set off with spaces corresponds to the 2nd argument to
WEEKNUM.