Calculate 52 weeks
 

I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return the
"1" for this week range, or "2" for week #2 range. How do I do this?

Is this what you mean

=INT((TODAY()-A2)/7)+1

--
HTH

Bob Phillips

"Rainey" wrote in message
...
I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our
accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return the
"1" for this week range, or "2" for week #2 range. How do I do this?

Not really,
I tried to use the formula WeekNum. It calculated up to a 53 week, and then
there was no week 1! And, I'm trying to do fiscal year 2005, and not 2004,
but with the week sum, it shows 2004 and 2005.
The reason 2004 data is in there is because week #1 is from 12/26/2004 -
1/1/2005
I had to copy Dec 2004 data and now it is throwing off my 2005 fiscal totals.

"Bob Phillips" wrote:

Is this what you mean

=INT((TODAY()-A2)/7)+1

--
HTH

Bob Phillips

"Rainey" wrote in message
...
I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our
accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return the
"1" for this week range, or "2" for week #2 range. How do I do this?

I'm messing with something like:
=IF(G912/25/2004,WEEKNUM(G9,1),"")...the only problem is that it doesnt
recognize 12/25/2004. I have tried 12/25/2004 in quotations, but it doesnt
work.

"Rainey" wrote:

Not really,
I tried to use the formula WeekNum. It calculated up to a 53 week, and then
there was no week 1! And, I'm trying to do fiscal year 2005, and not 2004,
but with the week sum, it shows 2004 and 2005.
The reason 2004 data is in there is because week #1 is from 12/26/2004 -
1/1/2005
I had to copy Dec 2004 data and now it is throwing off my 2005 fiscal totals.

"Bob Phillips" wrote:

Is this what you mean

=INT((TODAY()-A2)/7)+1

--
HTH

Bob Phillips

"Rainey" wrote in message
...
I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our
accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return the
"1" for this week range, or "2" for week #2 range. How do I do this?

Take a look at http://www.cpearson.com/excel/weeknum.htm at ISO week numbers

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rainey" wrote in message
...
Not really,
I tried to use the formula WeekNum. It calculated up to a 53 week, and
then
there was no week 1! And, I'm trying to do fiscal year 2005, and not
2004,
but with the week sum, it shows 2004 and 2005.
The reason 2004 data is in there is because week #1 is from 12/26/2004 -
1/1/2005
I had to copy Dec 2004 data and now it is throwing off my 2005 fiscal
totals.

"Bob Phillips" wrote:

Is this what you mean

=INT((TODAY()-A2)/7)+1

--
HTH

Bob Phillips

"Rainey" wrote in message
...
I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our
accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return
the
"1" for this week range, or "2" for week #2 range. How do I do this?

On Mon, 20 Jun 2005 06:36:03 -0700, Rainey
wrote:

I am trying to take a date range 6/26/2004-1/31/2005
and get it to return a specific week number in the 52 weeks. Our accounting
week #1 starts at 5/26/2004-1/1/2005. And so on. I NEED it to return the
"1" for this week range, or "2" for week #2 range. How do I do this?

Could you clarify exactly what you want?

The date range you specify for starting with week one encompasses over 31
weeks. Do you want to return a number of "1" for all of those weeks? Or
something else?


--ron

I've implemented two functions that convert correctly (according to ISO
8601) from a date to a week (=DateToWeek) and from a week to a date
(=WeekToDate). See either
http://www.pvv.org/~nsaa/ISO8601.html
or
http://www.pvv.org/~nsaa/excel.html#22

Look for
Public Function DateToWeek(ByVal datDate As Date, _
Optional ByVal bytTruncFormat As Byte = 0, _
Optional ByVal bytShortLongFormat As Byte = 0) As String

Public Function WeekToDate(ByVal strWeek As String) As Date