Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate EDATE using days not months? | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
HOW TO CALCULATE NUMBER OF WEEKS BETWEEN TWO GIVEN DATES(MAY BE . | Excel Worksheet Functions | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
How do you calculate the nth root of a number in Excel 2003? | Excel Worksheet Functions |