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

  • Thread starter Thread starter Sundance
  • Start date Start date
S

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

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")
 
=TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+1,"m/d")
&" - "&TEXT(DATEVALUE(MID(A1,FIND("-",A1)+2,8))+7,"m/d/y")
Click to expand...

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")
 
Back
Top