Excel Footer with Formatted Date

It’s Fancy Footer Friday! Check with your boss – maybe you can leave early to celebrate.

This week, I’ve been working on Excel printed reports, and one of my clients wanted some fancy features in the footer. There are built-in footer options in Excel, but my client wanted to pull information from the worksheet, and format the date, so we needed some footer programming.

Add Order Information to Footer

In this example, I’ve created an order sheet, and named the cells where the Order Date, Customer Name, and Order Number are entered.

ExcelFooterMacro01

Run the Excel Footer Macro

Then, the SetFooter code, shown below, is pasted into a module in the workbook. When you run the SetFooter macro, it adds the customer name and order number in the left footer. The line break is created with the Chr(10) code.

ExcelFooterMacro02

In the right footer, the order date appears, formatted as dd-mmm-yyyy.

ExcelFooterMacro03

Excel Footer Sample Code

If you download the sample workbook, you’ll see the code that is used to add the Order Date, Customer Name, and Order Number to the footer.

excel footer macro code

Modify the Code

Copy the code to your workbook, then modify the code, so it has the sheet name and range names used in your workbook.You’ll have to change the following lines of code.

Set wsO = wb.Sheets("Orders")
dtmDate = .Range("OrderDate").Value
strCust = .Range("CustName").Value
strOrder = .Range("OrderNum").Value

You can also change the date format in the code, if you prefer something different. For example, change the date format to the following.

Format(dtmDate, "yyyy-mm-dd")

You could change the position of the items too — put the date in the Left Footer, and the other information in the Right Footer.

Download the Sample File

To get the VBA code, and test the custom footer macro, you can download the Excel Footer Macro sample workbook. The file is in Excel 2007/2010 format, and zipped. When you unzip the file and open it, enable macros, so you can run the code.

Watch the Excel Footer Macro Video

To see the steps for setting up the named cells, and an explanation of how the code works, you can watch this short Excel video tutorial.

Or watch it on YouTube: Excel Footer with Worksheet Data and Formatted Date

_____________________

 

You may also like...

7 Responses

  1. Contextures Blog » Excel 2010 Print Preview Problems says:

    […] Excel Footer with Formatted Date […]

  2. Clarity says:

    An excellent post.

    I have created a simular macro for a corporate client who requires all spreadsheets and documents to have certain pieces of information in the footer (level of confidentiality, date and number of pages) but haven’t needed to pull anything from the spreadsheet. Pulling the text from within the spreadsheet rather than hardcoding it in the VBA adds a nice element of flexibility.

    Thank you.

  3. General Ledger says:

    For a long time I have been very disappointed by Excel’s options for formatting the date in headers and footers. Using VBA appears to be the best way.

    The date format in the header or footer is controlled by the setting for the Short Date in your operating system. To edit the setting in Windows 7, go to Regional and Language Options. Select the Formats tab. Here is a drop down list of the predefined regional settings. To change a specific setting, select the Customize this format… button. The Customize Regional Options window opens. The tab options are Numbers, Currency, Time and Date. Select Date to customize the date formatting. Examples of how the current formatting for the Short and Long Dates appear here as well as the ability to customize the formatting. Edit to your desire. Note there is a Reset option to restore the standard settings so don’t be afraid of making a mistake.

    Edit the Short Date as you will but Excel does not necessarily follow your directions. I was never able to get the name of the month (either abbreviated or spelled out in full) to appear in the header or footer. Excel always displayed the month as a number. For this reason using VBA may be the only option to getting what you want.

  4. Ariel says:

    What version of Excel is this code written for? I have written a similar one for Excel 2010 which does not work. Thanks for a reply.

  5. Jennifer says:

    All I want is in the Footer Month and Year without day. But I have spent so much time on this I have to get back to the real job.

  6. Magic says:

    Hi,
    With Excel 2010 I would have to save an XLSM file to add macro functionalies, but maceros are not recommended for Excel in my Company. So I also tried to adjust the “short date” format in the “Region and Languages” panel of Windows 7 within a XLS file.
    I made the same experience that in Excel 2010 the date fomat does not exactly follow the settings. Only a little, which is really poor. For confirmation of elder versions of office programs I did the same in a VISIO document with VISIO2003, which I still have: here it works as intended. I would say this is symptomatic for microsoft product updates: It getting worse and worse.
    On my private computer I now decided to switch to Linux and Open Office. Lets see what I find there…

  7. Lara says:

    Can you write a Macro to insert a footer with a company address only? I have written a macro for the header in 2003-2007 version but Excel is having problems recording my footer address.

Leave a Reply

Your email address will not be published. Required fields are marked *