Excel (Week of) dates in header automated 2

[cjbrown815]

Hello,
Excel 2007,
I'm trying to get the header to display last weeks date Monday through Sunday automatically. In the header I keep having to look at the calendar and adjust the dates manually.

Today I have a report that reads in the header

"Weekly Report - week of 11/01/2010 to 11/07/2010"

How can I get excel to fill in those dates automatically? Or can I do a formula that will display the second date based on what I put in in the first date field. or visa-versa???

Thanks for the help.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[dhulbert]

You can reference a specific cell from the header or footer using a bit of VBA code, so you could calculate the 2 dates you require then use the VBA code to reference them.

Use a variation on this

Sub UpdateFooter()
   ActiveSheet.PageSetup.Leftfooter = Range("A1").Value
End Sub

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SkipVought]

Hi,

This Monday
[tt]
=INT((Today()-2)/7)*7+2
[/tt]
So last monday is...
[tt]
=INT((Today()-9)/7)*7+2
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

So I should put Skips formula in a cell and use dhulberts code to refernce it in the header?

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931