Results 1 to 3 of 3
  1. #1
    chrisben
    Guest

    Date format in header/footer

    Using Excel 2000 (9.0.3821 SR-1) under Win98SE. My regional setting for short date is dd MMM yyyy (space as separator). Setting for long date is dddd, dd MMMM yyyy.
    When I insert the date into the Excel print header it inserts it as 28 02 2001 i.e. it knows all about the space separator but does not seem to want to look at the format itself. Any ideas? I know that the formatting used to be OK bu cant think of anythin that I've done recently which would have any effect on Excel formatting...
    Many Thanks
    Chris Bentley

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in header/footer

    I am not sure about this, but I am afraid that the &[Date] or &D which can be added to the header or footer of a spreadsheet is quite limited in formatting.
    With the small macro below, you can add the date in the format you want (change the format according to your requirements). You can change the macro to have the date in the CenterHeader or RightHeader or in the Footer (left, center and right).

    Sub Date_in_Header()
    Dim Datum As Variant
    Datum = Format(Date, "dddd, dd/mm/yyyy")
    With ActiveSheet.PageSetup
    .LeftHeader = Datum
    End With
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in header/footer

    The date formatting in the header/footer seems to act very strangely. It seems to get the separator character and the year format from the regional settings short date format, but it ignores the month format. The VBA code, inserted in the Workbook BeforePrint event will insert the current date into the center header for all selected sheets (the sheets being printed) in the format you indicated you want.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oSheet As Worksheet
    For Each oSheet In ActiveWindow.SelectedSheets
    oSheet.PageSetup.CenterHeader = Format(Date, "dd MMM yyyy")
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •