Can I change the date FORMAT in the FOOTER of an Excel Worksheet?

Factual Questions
1

OK, a little info first. This problem I am about to describe is one I have encountered at work. I am at home now and I don’t ever post from work so I am going to try to describe it as best I can and then hopefully one of you smart people will post the answer sometime tonight and I can try it on my work computer tomorrow.

At work I have Windows NT 2000 (Professional?). I know how to format Headers and Footers in Excel Worksheets so that, for instance, the name of the file (&File) or of the sheet (&Sheet) is automatically put at the bottom of the page. I even know how to change the font size, italicize or bold it if I should so desire.

The problem arises when I try to put the date I printed out the sheet (&Date) in the footer. This is a feature that comes in real handy for me but the problem is the format of the date…Excel wants to print it out in an ambiguous format (04-17-05) instead of my preferred format which would be 17 Apr 05. This is a problem because the FDA and its European counterpart prefer that we use unambigous date formats in all of our work.

I know how to format the dates in the cells themselves…that would be a trivial fix but there doesn’t seem to be an equivalent way to format the date in the footer.

I’ve been all through the Header and Footer menu and cannot find a way to mandate the format of the date…it seems to default to its preffered (ambiguous) way. I have also tried changing the preferences in the Control Panel and putting my preferred date format in there but it doesn’t seem to help.

So how about it…is there a way to do this or am I stuck with dates formatted the way Microsoft wants them done?

2

There are two solutions to this, and if you would have Googled it - it would have shown up as the first search result… :wink:

Basically, either you change your systems regional settings, OR, you make a macro that sets the format of the date for you:

If you don’t know how to create a macro:

  1. Open the TOOLS menu.
  2. Choose “Visual Basic Editor” from the MACRO submenu
  3. In the empty window, paste the above code.
  4. Choose Run Sub from the RUN menu. (Or press the “play” button)
3

I thought about changing the system regional settings but the Help section implied that that might unintentionally change a lot of other things as well.

So no way other than a macro, huh?

4

Well, yeah, if you use Date format values in other worksheets and in other applications such as Access - changing the global date format might cause some unwanted side-effects.

The simplest by far is to just copy that macro into the VBA editor and run it when needed.

5

You can use the built-in Event Procedures that fire in response to certain actions:

(from Excel VBA Help with RealTronic’s code inserted)


BeforePrint Event
Occurs before the workbook (or anything in it) is printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
**Cancel**   False when the event occurs. If the event procedure sets this
argument to True, the workbook isn't printed when the procedure is finished.

Example:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  ActiveSheet.PageSetup.CenterHeader = Format(Now, "dd mmm yy")
End Sub

So, this runs automatically for you.