Forum Discussion

Joe Cangelosi's avatar
Joe Cangelosi
Copper Contributor
May 02, 2018
Solved

Format Date in header and footer

Hi everyone,

I like to stamp PDFs of excel docs with the current time and date via the footer (or header).

 

The "insert current date" function inserts the date in my system's default (DD/MM/YYYY) but I would prefer if I could customize it per document.

 

Any idea how this would be possible? TIA!!

excel
Formatting
  • Haytham Amairah's avatar
    Haytham Amairah
    May 02, 2018

    Hi Joe,

     

    It seems that there is no option to change the format through the Current Date and Current Time elements.

    They always give you the date and time based on the default date format of the OS.

    The format will not change unless you changed the default date format of the OS.

     

    Regards

     

14 Replies

Sort By
  • Kasra_Afrough's avatar
    Kasra_Afrough
    Copper Contributor
    Dec 13, 2023
    Hello dear Joe,
    You can use below code into "Worksheet_Activate" section in Excel (any version).

    For Left Header Section:

    Private Sub Worksheet_Activate()
    ActiveSheet.PageSetup.LeftHeader = "&""Arial,Standaard""&16Date: " & Format(Now(), "d mmmm yyyy")
    End Sub

    OR
    For Right Header Section:

    Private Sub Worksheet_Activate()
    ActiveSheet.PageSetup.RightHeader = "&""Arial,Standaard""&16Date: " & Format(Now(), "d mmmm yyyy")
    End Sub

    Note: The number between "&" and "Date" (16) is the font size.
    You can change it as you wish!
    ______________
    When you active (select) this sheet (with any name), the VBA set the date format of this sheet automatically and when you go to "Print Preview", the date will set based on your formatting order and current date.
    Enjoy!
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 02, 2018

    Hi Joe,

     

    It seems that there is no option to change the format through the Current Date and Current Time elements.

    They always give you the date and time based on the default date format of the OS.

    The format will not change unless you changed the default date format of the OS.

     

    Regards

     

    • James_Kosalos's avatar
      James_Kosalos
      Copper Contributor
      Sep 12, 2020

      It would be great if in fact this were true; unfortunately it is not.  Excell doe NOT respect the date format set in the OS.  I have been fighting this for years.  Our arrogant, egocentric developers seem to have missed the fact that DD/MM/YYYY is NOT the international format.  Date is a mixed radix number that is best presented in either an ascending or decending sequence.  At the very least, there should be a way to use alphanumerics for the month to avoid confusion no matter the sequence.  For instance, DD/MMM/YYYY or MMM,DD/YYYY.  It appears this cannot be done with Excel.  Too bad.  Haytham Amairah 

      • Kasra_Afrough's avatar
        Kasra_Afrough
        Copper Contributor
        Dec 12, 2023

        Hello dearJames_Kosalos and dear Joe Cangelosi 
        You can use below code into "Worksheet_Activate" section in Excel (any version).

        For Left Header Section: 

         

         

        Private Sub Worksheet_Activate()
ActiveSheet.PageSetup.LeftHeader = "&""Arial,Standaard""&16Date: " _
& Format(Now(), "d mmmm yyyy")
End Sub

         

         


        OR for Right Header Section:

         

         

        Private Sub Worksheet_Activate()
ActiveSheet.PageSetup.RightHeader = "&""Arial,Standaard""&16Date: " _
& Format(Now(), "d mmmm yyyy")
End Sub

         

         


        Note: The number between "&" and "Date" (16) is the date's font size.
        You can change it as you wish!
        ______________
        When you active (select) this sheet (with any name), the VBA set the date format of this sheet automatically and when you go to "Print Preview", the date will set based on your formatting order and current date.
        Enjoy!

Resources