is there a formula for "Week of August 4-8'

[Teacher_Becky]

I am creating a teacher lesson plan book. I've been able to do some automatic
filling and other simple formulas for workday dates. However, I can't think
of a way, short of typing each character, to have the phrase "Week of..."
generate dates, and only workday dates - no Saturday/Sunday. So, the heading
on each page would be 'Week of August 4-8', next would be 'August 11-15', and
so on through to June of next year. I only know a couple formulas, and would
like to get better at trying to consider more possibilities by myself. So any
help or insight would be appreciated.

Ideally I would like to be able to use this same file next year and change
the 1st date and have all the others change.

Thanks!

 

[Teacher_Becky]

Ooops - Using 2007.

 

[Billy Liddel]

Becky

You will need to enter the first date somewhere, I used F1.
The header for the first sheet is the formula:

="Week of "&TEXT(F1,"mmmm dd")&TEXT(F1+4,"-dd")

On sheet2 F1 enter =Sheet1!F1+7 to get the next Mondays date.
On sheet3 the formula in =Sheet2!F1+7

And just copy the Header accross all sheets.

Regards
Peter Atherton

 

[Billy Liddel]

Becky
If you do not mind having a try at VBA here is a simple function that will
help you put the calculation date in each sheet.

Function SheetOffset(Offset, Ref)
' Returns cell contents at Ref, in the sheet offset
Application.Volatile
SheetOffset = Sheets(Application.Caller.Parent.index _
+ Offset).Range(Ref.Address)
End Function

Press ALT + F11, Insert, Module and paste the code. Return to the workbook.

Say you enter the start date in A1 of the first sheet. In Sheet2 enter the
formula:
=sheetoffset(-1,A1)+7 This will take the value of the first sheet 4 Aug 2008
and add seven days to it.

Select Both formulas, press Ctrl + C and select all the sheet tabs (hold
shift and click the last sheet tab. Press enter to copy the formula into each
selected sheet.

Without this function you would have to change the reference on all the
other sheets.

Regatrds
Peter Atherton