Excel 2000 Header Date Format..

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?

Gord Dibben wrote:
> 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
>
> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>
>> 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.
>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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.

Gord Dibben wrote:
> 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
>
> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>
>> 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.
>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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 wrote:
>
> 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.
>
> Gord Dibben wrote:
> > 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
> >
> > On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
> >
> >> 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.
> >
>
> --
> JD..
 >> Stay informed about: Excel 2000 Header Date Format.. 

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
on what date format I want to insert into the header or footer.

Dave Peterson wrote:
> 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 wrote:
>> 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.
>>
>> Gord Dibben wrote:
>>> 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
>>>
>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>>>
>>>> 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.
>> --
>> JD..
>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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 wrote:
>
> 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
> on what date format I want to insert into the header or footer.
>
> Dave Peterson wrote:
> > 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 wrote:
> >> 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.
> >>
> >> Gord Dibben wrote:
> >>> 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
> >>>
> >>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
> >>>
> >>>> 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.
> >> --
> >> JD..
> >
>
> --
> JD..
 >> Stay informed about: Excel 2000 Header Date Format.. 

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 wrote:
> 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 wrote:
>> 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
>> on what date format I want to insert into the header or footer.
>>
>> Dave Peterson wrote:
>>> 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 wrote:
>>>> 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.
>>>>
>>>> Gord Dibben wrote:
>>>>> 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
>>>>>
>>>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>>>>>
>>>>>> 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.
>>>> --
>>>> JD..
>> --
>> JD..
>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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.


JD wrote:
>
> 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 wrote:
> > 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 wrote:
> >> 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
> >> on what date format I want to insert into the header or footer.
> >>
> >> Dave Peterson wrote:
> >>> 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 wrote:
> >>>> 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.
> >>>>
> >>>> Gord Dibben wrote:
> >>>>> 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
> >>>>>
> >>>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
> >>>>>
> >>>>>> 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.
> >>>> --
> >>>> JD..
> >> --
> >> JD..
> >
>
> --
> JD..
 >> Stay informed about: Excel 2000 Header Date Format.. 

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!

Dave Peterson wrote:
> 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.
>
>
> JD wrote:
>> 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 wrote:
>>> 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 wrote:
>>>> 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
>>>> on what date format I want to insert into the header or footer.
>>>>
>>>> Dave Peterson wrote:
>>>>> 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 wrote:
>>>>>> 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.
>>>>>>
>>>>>> Gord Dibben wrote:
>>>>>>> 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
>>>>>>>
>>>>>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>>>>>>>
>>>>>>>> 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.
>>>>>> --
>>>>>> JD..
>>>> --
>>>> JD..
>> --
>> JD..
>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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 nowwrote:

>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!
>
>Dave Peterson wrote:
>> 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.
>>
>>
>> JD wrote:
>>> 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 wrote:
>>>> 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 wrote:
>>>>> 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
>>>>> on what date format I want to insert into the header or footer.
>>>>>
>>>>> Dave Peterson wrote:
>>>>>> 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 wrote:
>>>>>>> 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.
>>>>>>>
>>>>>>> Gord Dibben wrote:
>>>>>>>> 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
>>>>>>>>
>>>>>>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>>>>>>>>
>>>>>>>>> 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.
>>>>>>> --
>>>>>>> JD..
>>>>> --
>>>>> JD..
>>> --
>>> JD..
>>
 >> Stay informed about: Excel 2000 Header Date Format.. 

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.

Gord Dibben wrote:
> 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 nowwrote:
>
>> 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!
>>
>> Dave Peterson wrote:
>>> 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.
>>>
>>>
>>> JD wrote:
>>>> 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 wrote:
>>>>> 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 wrote:
>>>>>> 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
>>>>>> on what date format I want to insert into the header or footer.
>>>>>>
>>>>>> Dave Peterson wrote:
>>>>>>> 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 wrote:
>>>>>>>> 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.
>>>>>>>>
>>>>>>>> Gord Dibben wrote:
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>> On Thu, 29 Jan 2009 09:37:33 -0600, JD wrote:
>>>>>>>>>
>>>>>>>>>> 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.
>>>>>>>> --
>>>>>>>> JD..
>>>>>> --
>>>>>> JD..
>>>> --
>>>> JD..
>
 >> Stay informed about: Excel 2000 Header Date Format..