Excel 2000 Header Date Format..

[JD]

I'm moving from MS Works to MS Office 2000.

In Excel, the date in the Header is all numbers, for example, today's
date is 29-01-09 but I want it to show 29 January 2009.

I can't figure out what to change to get the proper date format. I do
want the format as dd-mm-yyyy but I want the month to be January not 01.

I'm using Windows XP home edition SP3.

 

[Gord Dibben]

Excel takes the header date from your Windows short date settings.

You cannot change that setting to the format you want to see.

You can run a macro to set the date as you wish.

Sub header_date()
ActiveSheet.PageSetup.RightHeader = Format(Date, "dd mmmm yyyy")
End Sub


Gord Dibben MS Excel MVP

 

[JD]

I set my Windows short date settings this way:

Control Panel, Regional and Language Options, Regional Options tab,
Short date: 29-Jan-09

So even though it's set that way, Excel is going to replace the Jan with 01?

How do I run a macro?

 

[JD]

I used Help and I got it to work. Thanks!

Do I just run the Macro once or do I need to run it for each new Excel
spreadsheet?

MS Works didn't work like this. ;-)

 

[Dave Peterson]

You'll need to do it for each sheet in every workbook that you want.

And you'll want to do it each day, too. Otherwise, you'll be left with the old
date when you print.

 

[JD]

Will I have to delete the old date or will it over-write it?

Is there any other way to do what I want without using a macro?

The spreadsheet in MS Works doesn't behave this way. It gives me options

 

[Dave Peterson]

If you use the code that Gord provided, then it that header section willl be
overwritten each time you run the macro.

You can always do it manually.

Excel isn't Works.

 

[JD]

How do I add to the macro to also include the file name with three
spaces between the file name and the date?

Also, when I start this .xls file I get a warning about running macros
so can I somehow identify myself as the author of this macro and allow
it to run without the warning? If I can, how do I do that?

Or is there a better way to enter the file name and long date without
doing it manually?

 

[Dave Peterson]

You could digitally sign your workbook. I've never done this, but I think
excel's help describes the process.

Personally, I wouldn't want this in each workbook I use. It's redundant and
it'll make updates a real pain to catch up.

I'd use an addin that does the work. And I'd store this addin in my XLStart
folder. I'd name the workbook/addin "JDsUtils.xla".

If you want to try...

Start a new workbook
Put this in a general module:

Option Explicit
Public Const ToolBarName As String = "JD's Utilities"
Private Sub Auto_Open()
Call CreateMenubar
End Sub
Private Sub Auto_Close()
Call RemoveMenubar
End Sub
Private Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Private Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("FixOneSheet", _
"FixAllSheets")

CapNames = Array("Add Headers to this sheet", _
"Add Headers to All Sheets")

TipText = Array("Run this for just the activesheet", _
"Run this for all the sheets")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNames(iCtr)
.Style = msoButtonCaption
.TooltipText = TipText(iCtr)
End With
Next iCtr

End With
End Sub
Private Sub FixOneSheet()
Call DoTheWork(ActiveSheet)
End Sub
Private Sub FixAllSheets()
Dim sh As Object
For Each sh In ActiveWorkbook.Sheets
Call DoTheWork(sh)
Next sh
End Sub
Private Sub DoTheWork(sh As Object)
'to include the path, you could use
'sh.parent.fullname
sh.PageSetup.RightHeader _
= sh.Parent.Name & " " & Format(Date, "dd mmmm yyyy")
End Sub


Then back to excel.

File|SaveAs
choose Addin
and store it in your XLStart folder.

Close excel and restart it.

Then open or create a new workbook and test it.

ps. If you decide you want to fiddle with the header, record a macro when you
change it manually. You may be able to pick out the pieces you need (like
font/font size) and included it in this code.

How do I add to the macro to also include the file name with three
spaces between the file name and the date?

Also, when I start this .xls file I get a warning about running macros
so can I somehow identify myself as the author of this macro and allow
it to run without the warning? If I can, how do I do that?

Or is there a better way to enter the file name and long date without
doing it manually?

 

[JD]

Sorry Dave but you lost me here. I'll have to read up on macros because
it's like I'm reading a foreign language. ;-)

Do you have a simple macro to add the filename and date separated by
three spaces?

I find this odd that a simple program like MS Works spreadsheet makes
adding a long date so easy and Excel makes it so hard.

Thanks for trying to help!

 

[Gord Dibben]

Get rid of first macro.

Place this code in Thisworkbook module.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = ActiveWorkbook.FullName _
& " " & ActiveSheet.Name _
& " " & Format(Date, "dd mmmm yyyy")
End Sub

Will run when you print any sheet.

And quit comparing Excel to Works.......that's three times now<g>


Gord

Sorry Dave but you lost me here. I'll have to read up on macros because
it's like I'm reading a foreign language. ;-)

Do you have a simple macro to add the filename and date separated by
three spaces?

I find this odd that a simple program like MS Works spreadsheet makes
adding a long date so easy and Excel makes it so hard.

Thanks for trying to help!

 

[JD]

I changed your macro to this:

Sub header_date()
ActiveSheet.PageSetup.CenterHeader = ActiveWorkbook.Name _
& " " & Format(Date, "dd mmmm yyyy")
End Sub

And that does what I want. FileName Long Date (header centered)

I'm sorry that I keep referring to the "other" program but I didn't need
no macro to insert the long date. ;-)

I guess I can live with the macro warning unless you have something to
add to the above macro so I can allow it to run without the warning and
not have to mess with the Thisworkbook module. Maybe down the road I'll
understand this better.

Thanks for your help.