SOLVED

Format Date in header and footer

Copper Contributor

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!!

14 Replies
best response confirmed by Joe Cangelosi (Copper Contributor)
Solution

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

 

Thank you!
Only VBA could help.

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 

@James_Kosalos Totally agree. Right now, my Windows Date is 22-Feb-2021 but the Date in Excel Header is 22-02-2021 whereas I want it to be 22-Feb-2021. I'm unable to find any way to change the format of the Date field in the Excel Header. I have tried Insert > Header and Footer, select Date. But the Number item on menu is dimmed. Looking forward to a solution! TIA. 

 

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

@Willy Lau 

Thanks Buddy, it's really helpful.

@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

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

@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?

PLEASE pass on this information if you find someone to help. I'm not that sure a VBA means, just want to able to auto date in footer as 01 Jan 2001...@ryanss 

It can be done using VBA (visual basic for applications) in Excel. This would require the Excel file to be saved with Macro enabled or .xlsm.

Once you have the file, with Macro enabled. Press ALT-F11 to open the VBA interface. On the Left hand side, Under "VBAProject"->"Microsoft Excel Object" click on "ThisWorkbook". Then add the following function in Window on the right (change name of worksheet as required):

Private Sub Workbook_Open()
Worksheets("Sheet1").PageSetup.RightHeader = Format(Date, "dd-mmm-yyyy")
End Sub

The example above is for Header Right. For Footer, it will be LeftFooter or CenterFooter or RightFooter.

Hello dear@James_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!

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!
1 best response

Accepted Solutions
best response confirmed by Joe Cangelosi (Copper Contributor)
Solution

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

 

View solution in original post