Changing date format in a footer

[Guest]

I have entered a date in a worksheet footer. I would like to change its
format from the default of dd-mm-yy to dd-MMM-yy. How can I do this ? Can I
change the default date format for all worksheets ?
TIA

 

[Gord Dibben]

John

Excel gets its footer date format from your Operating System Regional Options
short date settings.

You might be able to change that to your desired format of dd-MMM-yy.

I entered that format in my regional settings and I still get dd-mm-yy so
don't know how you would make out.

It would be a global setting and affect all programs and all worksheets.

Alternative would be to enter a formatted date in a cell and use that cell as
the date in your footer.

This would require a macro to set all sheets.


Gord Dibben Excel MVP

 

[Guest]

Thanks for the response. I had used the change in the regional settings to
get what I wanted for other programs, but as you have confirmed, it doesn't
seem to work for Excel. V. strange !
So, if anyone does know a way around this ?....

 

[Gord Dibben]

John

One workaround is a macro in a general module.

Sub DateInFooter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.RightFooter = Format(Date, "mm-DDD-yy")
Next ws
End Sub

Alternative....in a beforeprint routine in Thisworkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.RightFooter = Format(Date, "mm-DDD-yy")
Next ws
End Sub


Gord