Possible to change date format in header/footer?

S

StargateFan

Hi, here's something else I've never seemed to find the answer for.
In the header/footer area the date shows up in the format 6/2/2004 for
today, for example. Is there a way to be able to change the format to
June 6, 2004, or other?

Thanks!
 
D

DDM

Stargate, quick and dirty: The date in the header/footer follows the short
date format from the regional settings in Windows Control Panel. Change it
there and it will change immediately in Excel.

Take a look at this page http://www.mvps.org/dmcritchie/excel/pathname.htm
from David McRitchie's encyclopedic Web site for a discussion of how to use
VBA macros to control the content and formatting of headers and footers. Not
quite halfway down that very long page he notes that you can use a command
like this one:

ActiveSheet.PageSetup.RightFooter = Format(now(),"dd-mmm-yyyy")

to control the date format (look for the heading "Including a Date in a
Footer").
 
J

JE McGimpsey

One way:

Put this in your ThisWorkbook code module (right-click the workbook
title bar and choose View Code):

Private Sub Workbook_Open()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.PageSetup.LeftFooter = _
Format(Date, "mmmm d, yyyy")
Next wsSheet
End Sub
 
S

StargateFan

One way:

Put this in your ThisWorkbook code module (right-click the workbook
title bar and choose View Code):
Click to expand...

Phew, that was tough to find. I finally figured out that if it
minimized the window, that that bar is the workbook bar and _then_ the
View Code command appeared int he context menu! Neat trick. God,
it's tough being a newbie said:
Private Sub Workbook_Open()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.PageSetup.LeftFooter = _
Format(Date, "mmmm d, yyyy")
Next wsSheet
End Sub
Click to expand...

I'm definitely missing something. I tried this and also read up on
the page that DDM recommended, and all the advice is great! But how
does it then work? I even saved the doct and closed it and Excel and
went back in but the date format remained in the same standard style.

I'm missing how this is "activated". I kept the date and other
particulars the same in the footer. Perhaps the coding there changes?
Can someone point me to a webpage that tells in more detail how to do
this for those of us who don't know VBA? <g>

Thanks so much!
 
S

StargateFan

Stargate, make sure that macros are not disabled. Tools > Macros > Security.
Set to Medium. Then close the workbook and open it again. The macro is
activated whenever you open the workbook.
Click to expand...

Hi, no macros _definitely_ not disactivated as I have others in this
file besides this one and they work just fine.

<sigh> Okay, will have to bring file home (not really supposed to,
but what the heck) and see if I can track down what the problem is
here at home where I have a bit more time to do that sort of thing.
 
R

Rowie

Sub FooterDate()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmm yy")
End Su
 
S

StargateFan

Sub FooterDate()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmm yy")
End Sub
Click to expand...

Okay, I get the dunce cap, I know! <g>

Where does this go, pls?

Thanks for all the patience with this newbie! I have so much stuff
always on the go and some much new knowledge to stuff in that I wonder
how I manage some days! <g>

As much as I love computers, wish some days we could go back to
simpler times in the office! They never really appreciate all this,
 
J

JE McGimpsey

Take a look at David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


StargateFan said:
Sub FooterDate()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmm yy")
End Sub
Click to expand...

Okay, I get the dunce cap, I know! <g>

Where does this go, pls?

Thanks for all the patience with this newbie! I have so much stuff
always on the go and some much new knowledge to stuff in that I wonder
how I manage some days! <g>

As much as I love computers, wish some days we could go back to
simpler times in the office! They never really appreciate all this,
Click to expand...
 
Top