May 02 2018 02:28 AM
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!!
May 02 2018 06:04 AM
SolutionHi 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
Sep 12 2020 11:21 AM
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
Feb 22 2021 07:19 AM
@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.
Nov 04 2021 09:56 AM
Jul 20 2022 04:27 PM
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
Aug 02 2022 06:53 AM
@SPrcevich Where do you put this script? In the Windows registry??
Feb 18 2023 02:19 PM - edited Feb 18 2023 03:07 PM
Your script examples are what I am looking for (DD-MMM-YYYY). However, please excuse my lack of knowledge, how do you implement them?
Mar 20 2023 06:53 AM
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
Apr 04 2023 01:36 AM - edited Apr 04 2023 01:52 AM
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.
Dec 12 2023 08:28 AM - edited Dec 13 2023 05:17 AM
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!
Dec 13 2023 05:13 AM
May 02 2018 06:04 AM
SolutionHi 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