EXCEL Footer date formatting

[cmcgeorge]

The EXCEL 2007 date format in the footer using the function &[DATE] is very basic. ~ Where can I set the format to DD MONTH YYYY?

 

[Yahya]

Hi cmc
You need to use VBA code like this

Sub Footer()
    With ActiveSheet.PageSetup
        .CenterFooter = Format(Date, "dd mmmm yyyy")
    End With
End Sub

 

[cmcgeorge]

The VBA option listed is helpful, but it seems to mean that EXCEL 2007 does not offer footer date formatting.

 

[Peter_SSs]

The VBA option listed is helpful, but it seems to mean that EXCEL 2007 does not offer footer date formatting.

Are you saying that the code does work for you in Excel 2007? It does work for me.

Or are you just suggesting that there doesn't seem to be a non-vba way of doing it? If this is your suggestion then, yes, you are correct. I believe the date format applied just using the &[Date] code in the footer is the Short Date format as taken from your Region and Language settings in Control Panel.

 

[cmcgeorge]

Peter, The VBA workaround is fine, and yes, you have read my question correctly. Thanks for your definitive answer. You may close this thread down now.

 

[Peter_SSs]

You may close this thread down now.

We only 'Close' threads when there is a breach of the forum rules. We don't close threads when a satisfactory answer has been received because that would stop the cases where somebody with more knowlege comes along later and says, "All you need to do is this and that and you've done it". That scenarion is not uncommon in a public forum like this.

 

[jhadur]

Is this still the only way to format a date in a header/footer in Excel 2010? We are trying to create a default template for all our users but the date format needs to be May 9, 2011 not 5/9/2011.

 

[Peter_SSs]

Is this still the only way to format a date in a header/footer in Excel 2010?

Welcome to the MrExcel board!

As far as I am aware, nothing has changed in this regard in Excel 2010.

 

[Phil Smith]

I have a solution that works for me...

As requested by jhadur, this code shows today's date in the footer formatted as "June 11, 2011" as of using it today, obviously.

Sub FormatDate
 
     With ActiveSheet.PageSetup
 
          .CenterFooter= Format(Now(), "mmmm d, yyyy") 
 
     End With
 
End Sub

Hope it is of use to you....

Phil

 

[TorontoJim]

I just had Win7 and Office 2010 installed on my terminal at work. The Excel "Format()" function does not work no matter what scenario I try it in. I keep getting the error message on this (as an example)

MsgBox Format(Now(), "yyyy.mm.dd")

The error message is "Compile Error: Can't find project or library"