Header/Footer Date Format

[David]

Hi, I would like to have my date format in the header/footer to be
dd/MMM/yyyy. However the 'Date Button' in the header screen only gives me a
format of dd/MM/yyyy. My Regional settings are as i need them to be. Date
format in my actual worksheet is as i want it.....just not in the
header/footer ?

 

[Ron@Buy]

This is a bit basic but the way I've achieved this is to actually go into the
footer and type in the date exactly as you want it to appear.

 

[Roger Govier]

Hi David

You would have to use code to change it.

You could add the following code to the ThisWorkbook Module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterFooter = Format(Date, "dd mmm yyyy")
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Double click on the ThisWorkbook module in Project Explorer
Paste code into white pane that appears
Alt+F11 to return to Excel

It will get invoked automatically when you Print or do a Print Preview