Date Format in Footer

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Is there away to change the format of the date in the footer of an excel
document?

Keith in Las Vegas
 
Hi Keith,

One solution is to code it:

you can put the following code into the thisWorkbook object and everytime
you print the format will be what ever you code here:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterFooter = Format(Date, "M-DD-YYYY")
End Sub

All you need to do is modify the "M-DD-YYYY" any way you want. These are
just the date format codes you see when you choose Format, Cells, Number tab,
Date, then Custom. For example "mm/dd/yy" would be another option.

To add this code to the thisWorkbook object
1. Press Alt+F11
2. In the Project window, top left, double-click the thisWorkbook listed in
your file.
3. copy in the above code and modify the "mmmm-d-yy" portion as you desire.
Yes this ones different too.
 
You can update your footer using a macro. How you do this depends exactly
what you want to achieve.

This example updates the right footer of every worksheet in a workbook. The
example calls it every time the workbook is opened so that the correct
printed date shows every time.

'*******************************
Private Sub Workbook_Open()
call addFooterToAll()
End Sub

Sub addFooterToAll()

Dim sDtFmt As String
Dim ws As Worksheet

sDtFmt = "dddd dd/mm/yyyy" 'change to required format

For Each ws In Worksheets
With ActiveSheet.PageSetup
.RightFooter = "Printed " & Format(Date, sDtFmt)
End With
Next ws

End Sub
'*******************************

This site will show you the basics of editing VBA. Instead of Step 3, paste
the above code (inside the asterisks). Don't follow further steps. Just
save, close and re-open your workbook to see the result.

OR

to see the result without closing the workbook, just press a point within
the addFooterToAll() sub and press F5.

To change the format, update the section of code between the quotes after
sDtFmt = . For example, you might want mmmm dd yyyy to see October 14 2008.
 
You can make limited changes to the format in Regional Settings of Windows.

Note: "limited"

Best off going with the code for "unlimited"


Gord Dibben MS Excel MVP
 
Back
Top