Add date range

[Jonas Trevellion]

I'd like to add a column to my spreadsheet that includes a range of dates by
week, i.e.

COLUMN A
Week
16/02/09 - 22/02/09
09/02/09 - 15/02/09
02/02/09 - 08/02/09
26/01/09 - 01/01/09
etc

COLUMN B
Week Number
8
7
6
5
etc

Is there a function to do this automatically? I'm using Excel 2007 SP3

 

[Max]

Keep it simpler, list/fill down the startdates in col A, enddates in col B,
from row2 down. Then in C2, copied down: =WEEKNUM(A2)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Max]

WEEKNUM requires that the Analysis ToolPak add-in be installed and loaded.
(If the function is not available, then you'd get the #NAME? error in C2
down)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

 

[Shane Devenshire]

Hi Max,

He's using 2007, he doesn't need to install the Analysis ToolPak for the
functions. Those are now all incorporated in Excel proper. You only need to
attach the ATP if you are going to use the Data Analysis Tools.

 

[Shane Devenshire]

Hi,

I favor Max's idea of splitting the dates into two columns, however, here is
the formula to do what you wish with your data as is:

=WEEKNUM(LEFT(A1,FIND("-",A1)-1))

Warning: WEEKNUM is handled differently in different countries for that
reason this function has a second argument not shown here. You should check
the Help system to see which applies to your situation.

 

[Max]

Thanks for clarification, Shane.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[chris]

select a cell in column B and enter following formula in formula bar

=MID(A1,1,FIND("-",A1,1)-2)

Note: you need excel 2007 because it won't work in excel 2003


Chris