Formatting Date in Excel Footer

[lzweifel]

Hello, I did come across a very old thread that spoke of this but was unable to find it exactly as I am looking for. I would like to format my date in the footer to read... Printed: Wednesday, January 5, 2022

Thank you so much for any help, it is very much appreciated!

 

[Joe4]

You can go into the Custom Footer Section, and change the style any way you like (of course, you can also avoid the VBA code altogether and go in there and type in anything your want too).

Just note one thing that I should have mentioned earlier. When you run this code, it will put in the current date, but hard-code it. It will NOT be a dynamic date field that changes automatically like the TODAY funcition. Of course, you could run the VBA code again to update it.

If this is something where you ALWAYS want the footer to show the current date, even if you re-open the file next the next day, week, etc, we could put the VBA code in the Workbook_Open event, which run automatically whenever the file is open (as long as they enable VBA code).

 

[lzweifel]

You can go into the Custom Footer Section, and change the style any way you like (of course, you can also avoid the VBA code altogether and go in there and type in anything your want too).

Just note one thing that I should have mentioned earlier. When you run this code, it will put in the current date, but hard-code it. It will NOT be a dynamic date field that changes automatically like the TODAY funcition. Of course, you could run the VBA code again to update it.

If this is something where you ALWAYS want the footer to show the current date, even if you re-open the file next the next day, week, etc, we could put the VBA code in the Workbook_Open event, which run automatically whenever the file is open (as long as they enable VBA code).

What I am looking for is the time and date printed. I have the time on the left, date in the middle and pages to the right. But this is not something that the user will be friendly with, so I want it to work each time the file is opened and printed.

 

[Joe4]

OK, you could the code in the "Workbook_BeforePrint" event, which is VBA code that will run automatically whenever they go to print, as long as they have enabled VBA code in that workbook.

So, this code need to go specifically in the "ThisWorkbook" module in VBA. It will NOT work if you place it in any other module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterFooter = "Printed: " & Format(Date, "dddd, mmmm d, yyyy")
End Sub

If you want to add other things (you mentioned time in your last post, though your original post did not mention time), or change the formatting, that can be done in the VBA code too, though the formatting part gets kind of messy, so we would need to know EXACTLY what formatting you are trying to apply.

 

[lzweifel]

Its okay... the VBA makes it messy, and it isn't that important. So I will just use a regular date. What you provided worked perfectly, just didn't accept the style formatting.