Using formulas in Custom Header in Excel 1

[dhamspec]

Hi, I would like to use a formula in excel for an automatic date function and am not having much luck getting it to work. Specifically I want a past date. The below is what I would use if it was in a cell.

=TODAY()-7

Am I beating my head against a wall for nothing, is it simply not possible to use a function in a Custom Header, if it is possible what am I doing wrong. Or is there another way to get a past date in a Custom Header

Thanks in advance

 

[dcompto]

The only way I could figure out to do it, is to use VBA in the workbook module as a BeforePrint event...

Right-click the Excel X icon in the top left corner of the workbook's window (to the left of "File" on the menu).
Left click on View Code
Paste the following VBA code into the window:

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Dim sht As Worksheet 
For Each sht In ThisWorkbook.Sheets 
sht.PageSetup.LeftHeader = Format(Now() - 7, "mmmm d, yyyy")
Next sht 
End Sub

Press Alt+Q to return to your worksheet.

The date will be shown in the Header when you print the page or print preview.

 

[dhamspec]

Thanks so much for the reply. This works well