Function that returns a week number

Hi,
Is there a function that returns the week number for a date?
For example, if I enter 08/11/2007, the function will return week number 45.

=WEEKNUM(A1)

or

=WEEKNUM("11/8/2007")

HTH

Jason

Originally Posted by zb61

Edit: Actually I just put weeknum(Today())-weeknum("06/01/07") and it seems to work, any reason why it wouldn't?

Yes, as of 1st January 2008 that will give you a negative number. How do you define your week numbers? Does week 1 always start on the 1st of June and last 7 days? If so you could use a formula like

=INT((TODAY()-DATE(YEAR(edate(TODAY(),-5)),6,1))/7)+1

Originally Posted by daddylonglegs

Yes, as of 1st January 2008 that will give you a negative number. How do you define your week numbers? Does week 1 always start on the 1st of June and last 7 days? If so you could use a formula like

=INT((TODAY()-DATE(YEAR(edate(TODAY(),-5)),6,1))/7)+1

The fiscal year always starts on the first friday of June. So how can I change the formula to reflect that?

Assuming A2 contains a date - if you want today's date then A2 should contain the formula =TODAY()

=INT((A2-WEEKDAY(A2-5)-DATE(YEAR(EDATE(A2+1-WEEKDAY(A2-5),-5)),6,0))/7)+1

Originally Posted by daddylonglegs

Assuming A2 contains a date - if you want today's date then A2 should contain the formula =TODAY()

=INT((A2-WEEKDAY(A2-5)-DATE(YEAR(EDATE(A2+1-WEEKDAY(A2-5),-5)),6,0))/7)+1

So all the A2's should be Today()'s ?

I'm suggesting that you put =TODAY in cell A2 (or any other cell) then it makes the formula a little shorter but it's also possible to use TODAY() in place of each instance of A2 in the formula, yes

do I need to update that formula on year changes?