How to specify date or datetime format in header or footer?
When using Excel, you might often need to include the current date or time in your worksheet’s header or footer for purposes such as printing reports or keeping track of document revisions. By default, if you insert a date or time into the header or footer using the &[Date] or &[Time] code, Excel will display these elements using the date and time format specified by your Windows regional settings. This behavior means that you cannot directly customize how the date or time appears in the header or footer—such as displaying "April 23,2024" instead of "4/23/2024"—with the built-in functionality.
This limitation can be an issue if you need your documents to match specific corporate style guides, regional preferences, or personal preferences regarding date and time presentation. For example, you may want to show the date in a “Month Day, Year” format, or combine both the date and time (“April 23, 202418:05”) in your document headers or footers. The following screenshot demonstrates a customized date format in the worksheet header:
To address this, you will need to use VBA (Visual Basic for Applications) to programmatically set the header or footer to your desired date or datetime format. This method provides flexibility and allows you to bypass the restrictions of the built-in Excel header or footer tools.
Specify date or date time format in header or footer with VBA code
Specify date or date time format in header or footer with VBA code
To customize the date or date time format in the header or footer, you can use a simple VBA code. This solution is suitable when you require a flexible and personalized display format that is not limited by your system’s regional settings. Please follow these steps:
1. Open the worksheet where you want to insert and customize the date format in the header or footer. It is helpful to switch to Page Layout View so that you can easily preview how your headers and footers will appear during printing. To enable Page Layout View, click the Page Layout button located at the bottom right corner of the Excel window, as shown below:
Tip: While switching to Page Layout View is optional for running the VBA code, it is recommended if you want to visualize header/footer changes immediately.
2. Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor window.
3. In the VBA editor, click Insert > Module. This will create a new module. Copy and paste the following VBA code into the module window:
VBA code: Insert and format date in header or footer:
Sub add_date_header()
ActiveSheet.PageSetup.CenterHeader = Format(Date, "mmmm dd, yyyy")
End Sub
- This code will automatically insert the current date in your chosen format into the header. You can change CenterHeader to LeftHeader, RightHeader, LeftFooter, CenterFooter, or RightFooter if you wish to move the formatted date to a different part of the header or footer.
- The date format string, for example mmmm dd, yyyy (which produces "April 23,2024"), can also be customized to any standard VBA date format codes such as dd/mm/yyyy, yyyy-mm-dd, or others as required.
- Pay attention to the date format string syntax—using incorrect format codes could lead to errors or unintended results. Always check that your result matches your intent in Page Layout View.
4. After entering and reviewing your code, press F5 or click the "Run" button in the VBA editor to execute the code. The specified date format will be instantly reflected in your worksheet’s header or footer. You can check the result in Page Layout View, as shown in the following screenshot:
If you need the date to update dynamically each time the workbook is opened or printed, you will need to re-run the VBA code or assign it to a Workbook Open/BeforePrint event. This VBA-based method is manual, so the header/footer content does not automatically refresh with each new day unless the code is rerun.
Tips: To include the current time together with the date in a custom format, you can use the following VBA code. This is helpful when your reports require a timestamp, or when you want to track both the date and time of printing or export in your worksheet’s header or footer:
Sub Change_Format()
ActiveSheet.PageSetup.CenterHeader = Format(Now, "MMMM DD, YYYY HH:MM:SS")
End Sub
The result will be a header or footer that displays both the date and time in your specified format. For example, if you set the format to "mmmm dd, yyyy hh:mm"
, you will see a presentation like "April 23, 2024 18:05".
And you will get the following result:
Caution: When saving workbooks containing VBA macros, you must save your file as a macro-enabled workbook (.xlsm) to ensure your code is retained for future use. Some Excel settings may restrict macro execution; if you encounter errors, check your macro security settings under File > Options > Trust Center > Trust Center Settings > Macro Settings. If the header or footer does not update as expected, ensure you are running the macro on the correct worksheet and that your code refers to the appropriate header/footer section.
If you need to set up different date formats for different sheets, simply repeat the process on each relevant worksheet, adjusting the format string as needed. For recurring use, you can assign the VBA code to a button or shortcut for quick access.
Alternative approaches to customize header or footer date formats include:
- Manually typing date/time in header or footer (simple but static): You can enter any date or time in your desired format by manually double-clicking in the header or footer in Page Layout View. However, this approach does not update automatically when the worksheet is opened or printed. This is a quick solution for static documents but requires manual updating for new dates.
In summary, using VBA is the optimal solution when you want flexible, customized, and dynamic date or datetime formats in headers or footers. Be sure to double-check your format strings and always save your macros properly. If you encounter issues with outdated or unchanged date formats after running the code, double-check your macro security settings or try saving and reopening your workbook.

Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!