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)...
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

     

Willy Lau's avatar
Willy Lau
Iron Contributor
Nov 08, 2018
Only VBA could help.
  • SPrcevich's avatar
    SPrcevich
    Copper Contributor
    Nov 04, 2021

     

    Sub add_date_header()
    ActiveSheet.PageSetup.CenterHeader = Format(Date, "mmmm dd, yyyy")
    End Sub

    Willy Lau 

    • lmdorsay's avatar
      lmdorsay
      Copper Contributor
      Aug 02, 2022

      SPrcevich Where do you put this script? In the Windows registry??

      • ryanss's avatar
        ryanss
        Copper Contributor
        Feb 18, 2023

        lmdorsay Anhton SPrcevich 

         

        Your script examples are what I am looking for (DD-MMM-YYYY).  However, please excuse my lack of knowledge, how do you implement them?

    • Anhton's avatar
      Anhton
      Copper Contributor
      Dec 31, 2021
      Thanks Buddy, it's really helpful.
      • HansMoviat's avatar
        HansMoviat
        Copper Contributor
        Jul 20, 2022

        Anhton 

         

        I used the following to also change the font and size::

         

        Private Sub Worksheet_Change(ByVal Target As Range)
        Target.Worksheet.PageSetup.LeftFooter = "&""Arial Black,Standaard""&12Updated: " & Format(Now(), "d mmmm yyyy hh:nn")
        End Sub

Resources