SOLVED

Format Date in header and footer

%3CLINGO-SUB%20id%3D%22lingo-sub-189400%22%20slang%3D%22en-US%22%3EFormat%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189400%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3EI%20like%20to%20stamp%20PDFs%20of%20excel%20docs%20with%20the%20current%20time%20and%20date%20via%20the%20footer%20(or%20header).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%22insert%20current%20date%22%20function%20inserts%20the%20date%20in%20my%20system's%20default%20(DD%2FMM%2FYYYY)%20but%20I%20would%20prefer%20if%20I%20could%20customize%20it%20per%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20this%20would%20be%20possible%3F%20TIA!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-189400%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284227%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284227%22%20slang%3D%22en-US%22%3EOnly%20VBA%20could%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189486%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189486%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189472%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189472%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joe%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20there%20is%20no%20option%20%3CSPAN%20class%3D%22%22%3Eto%20change%20the%20format%20through%20the%20Current%20Date%20and%20Current%20Time%20elements.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThey%20always%20give%20you%20the%20date%20and%20time%20based%20on%20the%20default%20date%20format%20of%20the%20OS.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20format%20will%20not%20change%20unless%20you%20changed%20the%20%3CSPAN%20class%3D%22%22%3Edefault%20date%20format%20of%20the%20OS.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3ERegards%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1660853%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1660853%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20would%20be%20great%20if%20in%20fact%20this%20were%20true%3B%20unfortunately%20it%20is%20not.%26nbsp%3B%20Excell%20doe%20NOT%20respect%20the%20date%20format%20set%20in%20the%20OS.%26nbsp%3B%20I%20have%20been%20fighting%20this%20for%20years.%26nbsp%3B%20Our%20arrogant%2C%20egocentric%20developers%20seem%20to%20have%20missed%20the%20fact%20that%20DD%2FMM%2FYYYY%20is%20NOT%20the%20international%20format.%26nbsp%3B%20Date%20is%20a%20mixed%20radix%20number%20that%20is%20best%20presented%20in%20either%20an%20ascending%20or%20decending%20sequence.%26nbsp%3B%20At%20the%20very%20least%2C%20there%20should%20be%20a%20way%20to%20use%20alphanumerics%20for%20the%20month%20to%20avoid%20confusion%20no%20matter%20the%20sequence.%26nbsp%3B%20For%20instance%2C%20DD%2FMMM%2FYYYY%20or%20MMM%2CDD%2FYYYY.%26nbsp%3B%20It%20appears%20this%20cannot%20be%20done%20with%20Excel.%26nbsp%3B%20Too%20bad.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2155883%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Date%20in%20header%20and%20footer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2155883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F791446%22%20target%3D%22_blank%22%3E%40James_Kosalos%3C%2FA%3E%26nbsp%3BTotally%20agree.%20Right%20now%2C%20my%20Windows%20Date%20is%2022-Feb-2021%20but%20the%20Date%20in%20Excel%20Header%20is%2022-02-2021%20whereas%20I%20want%20it%20to%20be%2022-Feb-2021.%20I'm%20unable%20to%20find%20any%20way%20to%20change%20the%20format%20of%20the%20Date%20field%20in%20the%20Excel%20Header.%20I%20have%20tried%20Insert%20%26gt%3B%20Header%20and%20Footer%2C%20select%20Date.%20But%20the%20Number%20item%20on%20menu%20is%20dimmed.%20Looking%20forward%20to%20a%20solution!%20TIA.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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!!

7 Replies
best response confirmed by Joe Cangelosi (New 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.