week numbers

[ROAST]

if i enter a date into a cell how can i get it to display a week number?

[Yogi Anand]

=weeknum(A2)

[Juan Pablo González]

You cannot do that directly. You need to use the ToolPak Analysis AddIn, and use the WEEKNUM (I think this is the name, I don't remember it right) function.

If you need to use it in VBA, you can use the Format function like this:

?Format(Date,"ww")
21

"ww" is just like "mm" for month, but for weeks.

[IML]

Poor man's =weeknum(a1) (not requiring tool pak) is
=ROUNDUP((A1-DATE(YEAR(A1)-1,12,31))/7,0)+(WEEKDAY(A1) < WEEKDAY(DATE(YEAR(A1),1,1)))

Not fully test, but appears to work.

[ This Message was edited by: IML on 2002-05-22 16:03 ]

[Yogi Anand]

On 2002-05-22 16:03, IML wrote:
Poor man's =weeknum(a1) (not requiring tool pak) is
=ROUNDUP((A1-DATE(YEAR(A1)-1,12,31))/7,0)+(WEEKDAY(A1) < WEEKDAY(DATE(YEAR(A1),1,1)))

Not fully test, but appears to work.

[ This Message was edited by: IML on 2002-05-22 16:03 ]

Hi IML:
Thanks for thinking for the poor man! ...how about just:

=ROUNDUP((A2-DATE(YEAR(A2)-1,12,31))/7,0)

As in your case, I have also not fully tested it.

Regards!

Edit: IML, on noting it carefully, I see mine is just the first part of your total expression -- I am wondering would this much not suffice, or is the second part of your expression also needed!

[ This Message was edited by: Yogi Anand on 2002-05-22 16:54 ]

[ This Message was edited by: Yogi Anand on 2002-05-22 16:57 ]

[IML]

On 2002-05-22 16:52, Yogi Anand wrote:

Hi IML:
Thanks for thinking for the poor man! ...how about just:

=ROUNDUP((A2-DATE(YEAR(A2)-1,12,31))/7,0)

As in your case, I have also not fully tested it.

Regards!

Edit: IML, on noting it carefully, I see mine is just the first part of your total expression -- I am wondering would this much not suffice, or is the second part of your expression also needed!

[ This Message was edited by: Yogi Anand on 2002-05-22 16:54 ]

[ This Message was edited by: Yogi Anand on 2002-05-22 16:57 ]

I added that little boolean peice trying to replicate the weekday function. using 1/7/02 as an example, you'll see that's necessary for the somewhat weird way weekday calcs. Poor men rule!