[RESOLVED] [Excel 2003] Change format of Date/Time in Header/Footer

[Torc]

Currently when I set the values for the Header and Footer I am limited on the
Date and Time to using &[Date] and &[Time] which respectively give me, for
example, 12/11/2008 and 16:06

Is there any way that I can set the format to something else, similar to using
a Format(now,"dd mmmm yyyy") command.

[westconn1]

you can put a formatted date, but it will not update,

[Torc]

Hi westconn1,

I want the Date and Time in my footers to be the date/time when printed, so
inserting the actual date as formatted text is not an option.

Given the confusion that can easily arise when the month is given as a
numeric I want to be able to display the month using the Text name.

In Word I can insert a Field using my choice of formatting into a header or
footer, surely I should be able to do the same in Excel.

[Siddharth Rout]

I want the Date and Time in my footers to be the date/time when printed, so
inserting the actual date as formatted text is not an option.

Is this what you want?

Instead of Now below, capture the date and time the moment print command is used...

Sub Change_Format()
    'For left footer
    ActiveSheet.PageSetup.LeftFooter = Format(Now, "dd mmmm yyyy")
    
    'for Center footer
    ActiveSheet.PageSetup.CenterFooter = Format(Now, "dd mmmm yyyy")
        
    'for Right footer
    ActiveSheet.PageSetup.RightFooter = Format(Now, "dd mmmm yyyy")
End Sub

[Torc]

Hmm, not quite.

I believe if I was to use that that I would need to use a
Workbook_BeforePrint or Worksheet_BeforePrint command to update the
footer to ensure that I got the correct Date and Time.

Is there any way to change the format used by the &[Date] and &[Time]
values placed in the Custom Header and Custom Footer to give Date and Time?

[westconn1]

i believe the only way would be to put a formatted string each time, in the before print event, also you would need to declare the application with events for that to work

excel only stores the &D in the footer which is converted to date at printing, it does not even use system short date format

[Torc]

Pity, the formatting is for Workbooks which are being generated by Excel Workbooks.

One of the requirements for the generated Workbooks is that they be lean, mean and macro free.

Maybe I will be able to use the Before Print method another time.