Format date in footer to propercase?

[Thomas]

Hi,
I am trying to format the date inserted in the footer with &[Date].
I've searched the excel-groups, and it is my understanding that the
only way is through VBA.

This is what i have that works:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = Format(Date, "dd-mmm-yy")
End Sub

However i want to format the month so that dec becomes Dec. I know that
this is the standard for US settings, but i use Danish regional
settings and would rather not change these.

I have tried this:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = StrConv(Format(Date, "dd-mmm-yy"),
vbProperCase)
End Sub

But it does'nt work.
Any suggestions would be greatly appreciated.
thx
Thomas

 

[Frank Kabel]

Hi
for me
Format(Date, "dd-mmm-yy")
does return '15-Dec-2004'

But you may try the following
ActiveSheet.PageSetup.RightFooter = Format(date,"dd") & "-" &
StrConv(Format(Date, "mmm"), vbProperCase) & _
"-" & format(date,"yy")

 

[Thomas]

Hi Frank,

Thx a lot. It works.

This was a very fast answer. (Great) But i can not see my own question
(Im using google groups beta). I can only see your answer (which
offcause is most important). Can i ask what u use to read/answer the
group?

Thx again
Thomas

 

[Frank Kabel]

Hi
I use Outlook Express. See:
http://www.mvps.org/dmcritchie/ie/oe6.htm

 

[Tom Ogilvy]

? Format(Date, "dd-") & strConv(Format(Date,"mmm-yy"),vbProperCase)
15-Dec-04

 

[Thomas]

Hi Tom,

thx for your slightly shorter version )

Thomas

 

[Thomas]

Hi,

I have a supplementing question:
Thanks to your help i now have the date appearing as wanted, but i
would also like to change the font and fontsize of the date. I found
out that the second line below changes the font/size, but unfortunately
the two lines overrule each other. So i want to combine them into one
line somehow. I have tried all kinds of syntaxes but without succes.
Can anyone help?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = Format(Date, "dd-") &
StrConv(Format(Date, "mmm-yy"), vbProperCase)
ActiveSheet.PageSetup.RightFooter = "&""Verdana""&8 "
End Sub

Thx again
Thomas

 

[Tom Ogilvy]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "&""Verdana""&8 " & _
Format(Date, "dd-") & _
StrConv(Format(Date, "mmm-yy"), vbProperCase)
End Sub

 

[Thomas]

So all i needed was to combine the two expressions with an "&".
Kindda obvious - when you know it.
Thx for showing me the way ;o)
Thomas