Excel Date Format in Preview->Setup->Header/Footer

[GGleason]

One of the things that has bothered me about Excel is that I have not been able to change the date format located in the header.

To navigate to this area, go into Print Preview, click on the Setup, then click on the Header/Footer tab, then finally the Custom Header... button.

When you click on the calendar page icon, Excel places &[Date] in the box, but it seems that you don't have the option to change the format.

Has someone found a solution to this issue?

TIA,
GGleason

 

[ETID]

What format are you lookin for?

 

[GGleason]

What I was looking for was something like you can get with the following cell formula:

=TEXT(NOW(),"mmmm d, yyyy&quot

Thanks,
GGleason

 

[ETID]

Copy the following code into a vba module of your choice...
Note: VBA code can be triggered by an event such as workbook open or page selection.






Sub Custom_footer()


Select Case Left(Date$, 2)
Case Is = "01"
Mon$ = "January"
Case Is = "02"
Mon$ = "February"
Case Is = "03"
Mon$ = "March"
Case Is = "04"
Mon$ = "April"
Case Is = "05"
Mon$ = "May"
Case Is = "06"
Mon$ = "June"
Case Is = "07"
Mon$ = "July"
Case Is = "08"
Mon$ = "August"
Case Is = "09"
Mon$ = "September"
Case Is = "10"
Mon$ = "October"
Case Is = "11"
Mon$ = "November"
Case Is = "12"
Mon$ = "December"
End Select

My_date$ = Mon$ + " " + Mid(Date$, 4, 2) + ", " + Right(Date$, 4)
MsgBox (My_date$)

'***********************************************************************
'Change "ActiveSheet.PageSetup" with "Sheets("your sheet name goes here&quot.PageSetup"
'to refer to a specific sheet in the next line
'Also substitute Right, Center or Header as needed where .LeftFooter = My_date$
'Example: .CenterHeader = My_date$
'***********************************************************************

With ActiveSheet.PageSetup
.LeftFooter = My_date$
End With

End Sub

 

[ETID]

Oooops delete the line...MsgBox (My_date$), you won't need it.

 

[GGleason]

Thanks!

Here is a more efficient version of the code:


Sub MyHeaderDate()
Dim strMyDate As String

strMyDate = Format(Date, "mmmm d, yyyy&quot

With ActiveSheet.PageSetup
.LeftHeader = strMyDate
End With

End Sub


This code places a fixed value in the header. Is there a way to make the date dynamic?

Thanks,
GGleason

 

[ETID]

Cool...thanks to you too

 

[ETID]

this also works...

Sub MyHeaderDate()
With ActiveSheet.PageSetup
.LeftHeader = Format(Date, "mmmm d, yyyy&quot
End With
End Sub