VBA & Macro Problems

Use a Full Date Format in an Excel Header or Footer

Chanuka GeekiyanageChanuka Geekiyanage Nov 20, 2026 868 views

Question details

The user wants to display a full date, such as January 1, 2018, in an Excel header or footer.

Use a Full Date Format in an Excel Header or Footer
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs a readable full date in its printed header or footer instead of a numeric date.
Observed behavior
Excel's built-in date field may display a numeric date and does not offer the same custom date-format controls as Word.
Before you start

Open the workbook in desktop Excel and check which header or footer section you want to use. If you choose the VBA method, make sure you can save the workbook as a macro-enabled file and are allowed to run macros.

Solution 1Recommended

Update a Full Date Before Printing with VBA

Use a workbook print event to write the current date in a custom format when printing.

This example writes to the center footer of the active worksheet. It replaces any existing text in that section and updates when the print event runs, rather than behaving like Excel's live date field.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Add the print event

In the VBA editor's Project Explorer, open ThisWorkbook and add this code: Private Sub Workbook_BeforePrint(Cancel As Boolean) If TypeOf ActiveSheet Is Worksheet Then ActiveSheet.PageSetup.CenterFooter = Format(Date, "mmmm d, yyyy") End If End Sub

3
Save and test

Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted, select the worksheet, and use File > Print to test the footer.

Update a Full Date Before Printing with VBA
Check the scope and existing footer: Test on a copy first: the code overwrites the active worksheet's center footer. It does not automatically update every worksheet when multiple sheets are printed. For a header, replace CenterFooter with CenterHeader.
Free Microsoft Office alternative

Edit Spreadsheet Headers and Footers with WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and their headers and footers. Open existing Office files to make migration easier; it does not fix Excel's date-field formatting.

Edit spreadsheet headers and footers for printed documentsOpen and edit Microsoft Excel formats such as XLSX and XLSWork in a familiar spreadsheet interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel's built-in date field use a custom long-date format?

Excel does not provide the same flexible date-field formatting controls as Word. You can enter a full date manually or use VBA to write a formatted date before printing.

Can I show a full date without enabling macros?

Yes. Open Page Layout > Page Setup > Header/Footer, select Custom Header or Custom Footer, and type the date. You must update that text manually.

Will the VBA date update every worksheet?

The example updates only the active worksheet's center footer when the workbook's print event runs. Code designed for additional sheets is needed to update them too.

Why did my existing footer text disappear?

Assigning a value to CenterFooter replaces everything already in that footer section. Test on a copy and check the section's contents before running the macro.

Does the VBA date update continuously like the built-in date field?

No. The example writes the current date when the Workbook_BeforePrint event runs. If macros are disabled, the event cannot update the footer.