Calculate 52 weeks

R

Rainey

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?
 
R

Rainey

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.
 
R

Rainey

I'm messing with something like:
=IF(G9>12/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.
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
S

sandved

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
 
Top