Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
is there a formula for "Week of August 4-8'
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
is there a formula for "Week of August 4-8'
Ooops - Using 2007.
"Teacher_Becky" wrote: 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! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
is there a formula for "Week of August 4-8'
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 "Teacher_Becky" wrote: Ooops - Using 2007. "Teacher_Becky" wrote: 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! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
is there a formula for "Week of August 4-8'
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 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 "Teacher_Becky" wrote: Ooops - Using 2007. "Teacher_Becky" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FUNCTION TO RETURN "DAY OF THE WEEK" | Excel Worksheet Functions | |||
Display "this week" column headers w/date & day of week? | Excel Worksheet Functions | |||
Need to convert date to "day of the week only" | Excel Worksheet Functions | |||
How do I "Sum sales figure for a finacial month to currenct week"? | Excel Worksheet Functions | |||
Simple? Formula for "for the week starting Monday May Xxth" | Excel Worksheet Functions |