Get Week number from Date

[xami]

Hello All,

for my job I need to get week number from date (date format doesn´
matter). I know, that in excel (beggining 2003 edition) is simpl
function WEEKNUM. But unfortunetelly in our firm is used 2000 version
I´ve worked on solution for really long time (compare with anothe
issues i´ve been solving) and only one solution I´ve created is thi
formula:

=ROUND(((C1-DATE(YEAR(C1),1,1)+WEEKDAY(DATE(YEAR(C1),1,1),2)-1)/7),0)

it works (let´s try ) but, it has problems with right counting
Maybe if you check, then enter date 6/18/06 which is week 24 but m
formula counts 25. Does anybody know, what´s wrong, or, if possible an
other solution? If yes i will be glad for any response.

;

 

[Don Guillett]

Weeknum is available on my xl95, xl97sr2, & xl2002
you must use the toolpak addin

 

[Bob Phillips]

The Weeknum function also gives you 25 for 18th June. If you want vthe ISO
week number, use this formula

= INT((C1-DATE(YEAR(C1-WEEKDAY(C1-1) + 4),1,3) +
WEEKDAY(DATE(YEAR(C1-WEEKDAY(C1-1) + 4),1,3)) + 5) / 7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)