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?

 

[Steeviee]

In Canada, do you possibly have the French language pack? I found that I could use 'Format' in my code but when my colleagues in France used the file, it does not recognise Format.

I also had to add a macro to set a range to "FR" or "EN", depending on with pack was being used. Then all my formula had to be set to:

=TEXT("A1",IF(Lan="FR","JJ/MM/AA","DD/MM/YY"))

 

[TorontoJim]

It wasn't a French language issue, we are an English speaking organization and there are no French installs in our Unit at least. I had emailed the IT propellar heads about this and then started poking around.

I then realized that funcres.xla and atpvbaen.xla were not defined nor added in. Of course, the network is set so that I can't access the C drive to install these myself OR add them in myself (the workbook has to be accessible across the nework).

I emailed IT again and low and behold, now the Format() IS working however, both the processing add-ins are STILL NOT SHOWING.

Sometimes you just have to shake your head and submit to the pain.

 

[Steeviee]

Sometimes you just have to shake your head and submit to the pain.

I feel your pain mate.

 

[LL Daley]

Is there a way to change the font size when you do this, it is coming up as 10 pt, but I want 6 pt?
I used a variation of the code suggested above: ActiveSheet.PageSetup.LeftFooter = Format(Now, "m d")
Thanks
-LL Daley

 

[Peter_SSs]

Is there a way to change the font size when you do this, it is coming up as 10 pt, but I want 6 pt?
I used a variation of the code suggested above: ActiveSheet.PageSetup.LeftFooter = Format(Now, "m d")
Thanks
-LL Daley

Welcome to the MrExcel board!

Try this, where the red number gives the font size

ActiveSheet.PageSetup.LeftFooter = "&6 " & Format(Now, "m d")

 

[lonestarjeepin]

Both sets of suggested code in this thread work, but once executed the date/time in the footer remain static. With &[Date] and &[Time], the values are always current. Is there a way to use the current date and time with custom formatting?

 

[Peter_SSs]

Is there a way to use the current date and time with custom formatting?

Welcome to the MrExcel board!

Try something like this in the ThisWorkbook module, with your desired formatting set by the blue part of course.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  ActiveSheet.PageSetup.LeftFooter = Format(Now, "m/d/yy hh:mm")
End Sub

 

[pkacs]

Very green working with VBA code.

How do I add other elements to the footer? When I use the sample below, it wipes out my custom footer. I am trying to put in the left footer the &[Path]&[File] - &[Date] - &[Time], with the date and time formatted as mm.dd.yy - hh:mma/p. When I do the sample below (changing .CenterFooter to .LeftFooter), it does change the date & time format but wipes out the path & file. When I tried to add &[Path]&[File] I got a syntax error. I also want the font to be 8pt.

Any help would be greatly appreciated.

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

 

[Peter_SSs]

How do I add other elements to the footer? When I use the sample below, it wipes out my custom footer. I am trying to put in the left footer the &[Path]&[File] - &[Date] - &[Time], with the date and time formatted as mm.dd.yy - hh:mma/p. I also want the font to be 8pt.

Welcome to the MrExcel board!

Try

Sub myFooter()
  ActiveSheet.PageSetup.LeftFooter = "&8&Z&F - " & Format(Now, "mm.dd.yy - hh:mm am/pm")
End Sub

 

[pkacs]

Peter - Thank you! That worked perfectly.

Welcome to the MrExcel board!

Try

Sub myFooter()
  ActiveSheet.PageSetup.LeftFooter = "&8&Z&F - " & Format(Now, "mm.dd.yy - hh:mm am/pm")
End Sub