How create a continuous date range: 9/14-9/20/09, 9/21-9/27/09

[Sundance]

I would like to be able to type in a cell a date range such as a week: for
example: 9/14 - 9/20/09, and in the next cell type the next date range, being
the following week: for example: 9/21 - 9/27/09. Then highlight both cells
and have Excel calculate the rest of the continuous weekly date ranges for
the rest of the year. Is this possible in Excel?

 

[Bernie Deitrick]

You would be able to do this by enteriong a single starting date. For example, enter 9/14 as a date
into cell A2, then use this formula somewhere else:

=TEXT($A$2+7*(ROWS($A$1:A1)-1),"m/d - ") & TEXT($A$2+7*(ROWS($A$1:A1)-1) +6,"m/d/yyyy")

and copy down as far as you need.

Change the two references to $A$2 to the cell where you enter your starting date, using the $ $
style....

HTH,
Bernie
MS Excel MVP

 

[Glenn]

I would like to be able to type in a cell a date range such as a week: for
example: 9/14 - 9/20/09, and in the next cell type the next date range, being
the following week: for example: 9/21 - 9/27/09. Then highlight both cells
and have Excel calculate the rest of the continuous weekly date ranges for
the rest of the year. Is this possible in Excel?

With just "9/14 - 9/20/09" in A1, put this in then "next cell" and copy across/down:


=TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+1,"m/d")
&" - "&TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+7,"m/d/y")

 

[Teethless mama]

=TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+1,"m/d")

&" - "&TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+7,"m/d/y")

You can make formula shorter by remove "DATEVALUE".

=TEXT(MID(A1,FIND("-",A1)+2,8)+1,"m/d")&" -
"&TEXT(MID(A1,FIND("-",A1)+2,8)+7,"m/d/y")