Formulating dates by weeks

[smudgedwhiteink]

Hello, I would like to use a formula that allows me to type in a date is
(lets's say A1) and for it to be carried forward in A2, A3, A4.. and so forth
up to thirteen cells.

Example:
I will type July 1st - July 7th in A1, and automatically A2 will be
formulated to have July 8th - July 14th in it. (and so on and so forth)

If someone could help me with this.. AWESOME! I am stuck! Thanks

Oh, and one more, if it is answerable.. If I have a row, that need a running
tallie of only certain cells, (let's say, A1, A6, A12..) is there an easier
way to do this other then =SUM(A1, A6, A12,....)? Just thought I would ask!!

 

[OssieMac]

On the first question I would use 2 separate columns.

Insert the first date (Jul 1) in cell A1. In cell A2 insert the following
formula:-
=A1+7 and drag that formula down.

In cell B1 insert the following formula:-
=A1+6 and drag that formula down.

Now each time you change the date in A1 all the rest change.

You can format the dates to display just mths and days with number
format->date->custom and insert:-

mmm dd

 

[Rick Rothstein \(MVP - VB\)]

Are you up for a UDF (User Defined Function)?

Key in Alt+F11 to go to the Visual Basic editor and then click on
Insert/Module on its menu bar; then copy/paste this code into the code
window that appeared...

Function IncrementWeek(R As Range)
Dim EndDate As Variant
If R.Count > 1 Then Exit Function
EndDate = Mid$(R.Value, InStr(R.Value, " - ") + 3)
EndDate = CDate(Left(EndDate, Len(EndDate) - 2) & ", " & Year(Now))
IncrementWeek = MonthName(Month(CDate(EndDate) + 1)) & " " & _
Ordinal(Day(1 + EndDate)) & " - " & _
MonthName(Month(CDate(EndDate) + 7)) & " " & _
Ordinal(Day(7 + EndDate))
End Function

Function Ordinal(Value As Variant) As String
Ordinal = Value & Mid$("thstndrdthththththth", 1 - 2 * _
((Value) Mod 10) * (Abs((Value) Mod 100 - 12) > 1), 2)
End Function

Now, back at your worksheet, put this in A2 and copy across...

=IncrementWeek(A1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Okay, I couldn't let this one go.<g> Here is a worksheet formula that does
not require and VBA assistance. Just copy/paste this formula into A2 and
copy it across...

=TEXT(7+(LEFT(A1,FIND("-",A1)-4)&", "&YEAR(NOW())),"mmmm
d")&MID("thstndrdth",1+2*MIN(4,RIGHT(DAY(7+(LEFT(A1,FIND("-",A1)-4)&",
"&YEAR(NOW())))))*(MOD(DAY(7+(LEFT(A1,FIND("-",A1)-4)&",
"&YEAR(NOW())))-11,100)>2),2)&" - "&TEXT(13+(LEFT(A1,FIND("-",A1)-4)&",
"&YEAR(NOW())),"mmmm
d")&MID("thstndrdth",1+2*MIN(4,RIGHT(DAY(13+(LEFT(A1,FIND("-",A1)-4)&",
"&YEAR(NOW())))))*(MOD(DAY(13+(LEFT(A1,FIND("-",A1)-4)&",
"&YEAR(NOW())))-11,100)>2),2)

Rick

 

[Rick Rothstein \(MVP - VB\)]

I keep forgetting that newsreaders will break long text lines at blank
spaces... which is what happened with the formula I posted. Here is that
same formula deliberately broken so that the breaks do not occur on a blank
space (this should make it easier to reformulate into a single formula line
in the Formula Bar)...

=TEXT(7+(LEFT(A1,FIND("-",A1)-4)&", "&YEAR(NOW())),"mmmm d")&
MID("thstndrdth",1+2*MIN(4,RIGHT(DAY(7+(LEFT(A1,FIND("-",A1)-4)&
", "&YEAR(NOW())))))*(MOD(DAY(7+(LEFT(A1,FIND("-",A1)-4)&", "&
YEAR(NOW())))-11,100)>2),2)&" - "&TEXT(13+(LEFT(A1,FIND("-",A1)-4)&
", "&YEAR(NOW())),"mmmm d")&MID("thstndrdth",1+2*MIN(4,RIGHT(DAY(13+
(LEFT(A1,FIND("-",A1)-4)&", "&YEAR(NOW())))))*(MOD(DAY(13+(LEFT(A1,
FIND("-",A1)-4)&", "&YEAR(NOW())))-11,100)>2),2)

Rick