Calculating cell entry from a date range
Posted by Dave Harrison on February 15, 2002 4:27 AM
Hello, I need to split the year up into "weeks" i.e. "week 1" to "week 52". Based on an inputted date, I need to assign a "week number".
I have a table set up, each row giving the week, the start and end dates i.e.
Week 1 01/04/2001 07/04/2001
Week 2 etc etc
At the moment I use nested IF statements in 10 seperate columns (due to max limit of 7 nested statements) to work out whether an inputted date is within each "week".
This all works fine, but the file size is huge, is there another way in excel 2000, an equivalent of a data lookup table say.
All the best,
Dave.
Posted by Ed Acosta on February 15, 2002 6:49 AM
In Excel 97 there is the WEEKNUM() function. Based on the date this returns the week number 1 thru 52. May want to check if it is available in Excel 2000. This is part of the analysis tool pack that comes with Excel.
Posted by Mark W. on February 15, 2002 9:47 AM
Why would Week 1 end on July 4th? [nt]
Posted by Dave Harrison on February 17, 2002 6:34 AM
Re: Why would Week 1 end on July 4th? [nt]
>Why would Week 1 end on July 4th? [nt]
Ah , I see what you mean, it doesn't, it ends on the 7th of April. I'm in Britain so I'm using the format.
dd/mm/yyyy
Dave.
