[Solved] Date format in footers

[Krall]

I want to put the date in the footer of a printed spreadsheet. But I would like to have the date format in the footer match the date format in the spreadsheet (yyyy-mm-dd). But the format in the footer seems to default to mm/dd/yyyy. How do I change it? I tried edit/Headers and Footers. I can change the font, but there seems to be nothing to change the actual date format.

[Zizi64]

Please upload your ODF type sample file here.

[Krall]

I have attached a sample spreadsheet. The date in the sheet is YYYY-MM-DD, and the date in footer is MM/DD/YYYY.

[Villeroy]

This is a missing feature. https://sourceforge.net/projects/ooomacros/files/ is a collection of very old OOo macros from an extinct website. One of them is named "Calc Header Footer". The macro still works with Apache OpenOffice while being incompatible with LibreOffice. The problem with that macro is that it requires some customization within the Basic code. By default it sets the header/footer font to "Comic Sans" and applies its own set of header/footer attributes including a footer date in ISO format YYYY-MM-DD and some German strings.
It should be easy to write this in a different manner where you simply write a tag string like <TODAY YYYY-MM-DD> into the header and/or footer which is recognized by the macro and replaced with the current date in the given format. Or even smarter: Replace the tag with the string of some cell.

[RusselB]

When I open the attached spreadsheet, then look at the Page Preview, I see the date in the footer and it shows to me in the YYYY-MM-DD format.
Since the OP states that it is not in that format for them, and yet I do see it in that format, and without making any changes to the document, I'm wondering if this might be locale specific.
My locale setting is: Default - English (Canada)

[Villeroy]

My locale setting is: Default - English (Canada)

The default date format for the Canadian English locale is YYYY-MM-DD.

[RusselB]

My locale setting is: Default - English (Canada)

The default date format for the Canadian English locale is YYYY-MM-DD.

Exactly, thus the display setting for the date in the header/footer appears to be based on the current locale setting.

[Villeroy]

Yep. And this date in that particular place is unformattable.

[MrProgrammer]

My locale setting is: Default - English (Canada)

I just changed my locale to that setting. Thank you for the suggestion, RusselB! And thanks to Canada for choosing a sensible format. It's nicer to work with YYYY-MM-DD dates in the formula bar than the US setting of MM/DD/YY, especially after going through Y2K. I spent hundreds of hours reviewing/documenting/changing programs to ensure that at our company Y2K meant Yawn2K.

It seems unlikely that the US will convert to the metric system in my lifetime. Every other country except Liberia and Myanmar has done so. <sarcasm>We're in some great company there.</sarcasm>

[RusselB]

It seems unlikely that the US will convert to the metric system in my lifetime

<sarcasm>What? You mean you don't plan on living forever? </sarcasm>

[robleyd]

<sarcasm>What? You mean you don't plan on living forever? <sarcasm>

SARCParseException::PrevPost:line1 - unclosed tag

[RusselB]

Didn't realize that sarcasm was a valid tag that needed to be closed.... Learn something new every day.

[Villeroy]

It should be easy to write this in a different manner where you simply write a tag string like <TODAY YYYY-MM-DD> into the header and/or footer which is recognized by the macro and replaced with the current date in the given format. Or even smarter: Replace the tag with the string of some cell.

Of course it does not work like this because you can't update the tags anymore once they have been replaced. And writing a more generic macro is far from easy because each page style can have up to 12 header/footer strings since every page style has left, center, right positions for headers and footers and for left hand print pages with even page numbers and for right hand print pages with odd page numbers:
Left hand Footer Center
Left hand Footer Left
Left hand Footer Right
Left hand Header Center
Left hand Header Left
Left hand Header Right
Right hand Footer Center
Right hand Footer Left
Right hand Footer Right
Right hand Header Center
Right hand Header Left
Right hand Header Right

A page style's header or footer has a property "same content for left/right". If that property is set, the left hand strings will be ignored. Both sides show the right hand string.
--------------------------------------------------
I wrote some weird Basic code which seems to cover all these cases. It can be copied to any module in the global scope ("My Macros"). Embedded in a spreadsheet document it works as well.

Task: You want a formatted date and a formatted time appear in some headers/footers of a certain page style.
5 Considerations:
1) Which page style? Notice that the two built-in cell styles "Default" and "Report" have localized names. In a German GUI they are named "Standard" and "Bericht". We will use the English names of these two page styles regardless of the GUI language. We choose the "Default" page style for now.
2) Header or footer? Let's choose a header with both date and time.
3) Which position on that header? Let's put them in the center.
4) Let us assume that we are preparing for duplex printing (both sides of paper sheets). Let the date appear in the center of the left page header and the time in the center of the right page:
#2 ........ 2019-06-29 ....... left page || right page.......... 14:45:59 ....... #3
5) When do we want to update these headers? Let's say we want to test this every time when we save the document. Later we may update when the document is going to be printed, loaded whatever.

0. Install the below code to "My Macros" or embed it in the current spreadsheet document.
1. Fill out 2 cells with formula =NOW() and apply any formattings you want to see in the headers. Let's say we use Sheet1.A1 for the date and Sheet2.A2 for the time.
2. Add dummy content for 3 print pages at least. We want a proper print preview for 3 pages.
3. Get the properties of the "Default" page style, tab "Header", uncheck "same content left/right".
4. Push the edit button, notice that you get 2 tabs to edit the left and the right hand pages.
5. Put the page number in the left section of the left page and in the right section of the right side. You may also put some dummy strings in the center sections and format the whole header to your liking. This will show the page number on the outside corners of the respective even and odd print pages. The macro will fill out the center without touching any formatting attributes but overwriting any preset content.
6. Call menu:File>Properties... tab "Custom Properties".
7a. Define a text property named Default.LHC with text value Date Header: {Sheet1.A1} from cell Sheet1.A1
7b. Define a text property named Default.RHC with text value Time Header: {Sheet1.A2} from cell Sheet1.A2
8. Call menu:Tools>Customize... tab "Events" and assign the Main routine of macro code (wherever you saved it) to event "Document has been saved".
9. Do save the document.
10. Call the page preview and notice the time header.
Save the document again and notice the changed time header.

The property name Default.LHC in step 7a refers to page style "Default", Left hand page, Header, Center position. The value is a literal string with a cell reference in curly braces {Sheet1.A1} will be replaced with the visible text of that cell. Hint:] Instead of a cell address you can also put a range name in curly quotes. I recommend this because the reference remains intact when you rename the cell's sheet or when you move the cell.
If you check property "same content left/right" of page style's "Default" header, the left hand page setup will be ignored. Both sides will show the time stamp that we defined as the right hand header in step 7b.

And this is the Basic module:

Code: Select all

REM  *****  BASIC  *****

REM token starts with...
Const cTag1 = "{"
REM token ends with
Const cTag2 = "}"

Dim bReset As Boolean

Sub Main()
	oStyles = ThisComponent.StyleFamilies.getByName("PageStyles")
	oUDP = ThisComponent.DocumentProperties.UserdefinedProperties
	oPropInfo = oUDP.PropertySetInfo
	for i = 0 to oStyles.getCount() -1
		oStyle = oStyles.getByIndex(i)
		REM left page of style
		oContent = oStyle.LeftPageHeaderContent
		oStyle = oStyles.getByIndex(i)
		sName = oStyle.getName()
		sPropName = sName &".LHL"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.LeftText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".LHC"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.CenterText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".LHR"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.RightText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		oStyle.LeftPageHeaderContent = oContent
		
		REM right page of style
		oContent = oStyle.RightPageHeaderContent
		sPropName = sName &".RHL"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.LeftText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".RHC"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.CenterText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".RHR"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.RightText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		oStyle.RightPageHeaderContent = oContent

		oContent = oStyle.LeftPageFooterContent
		sPropName = sName &".LFL"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.LeftText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".LFC"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.CenterText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".LFR"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.RightText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		oStyle.LeftPageFooterContent = oContent
		
		oContent = oStyle.RightPageFooterContent
		sPropName = sName &".RFL"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.LeftText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".RFC"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.CenterText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		sPropName = sName &".RFR"
		if oPropInfo.hasPropertyByName(sPropName) then
			oText = oContent.RightText
			if bReset then
				sPropVal = ""
			else
				sPropVal = oUDP.getPropertyValue(sPropName)
			endif
			substituteCellString ThisComponent, sPropVal
			oText.setString(sPropVal)
		endif
		oStyle.RightPageFooterContent = oContent
	next i
End Sub

Sub clear_UDF_HeadersFooters()
	bReset = True
	Main
End Sub

Sub test_substituteCellString()
REM test with named cell or with cell address
Const cTestNamed As Boolean = 1
	if cTestNamed then
		sName = "DateCell"
	else
		sName = "Sheet2.A1"
	endif
	sTest = "Formatted Date: "& cTag1 & sName & cTag2 &" from "& sName
	print "INPUT :", sTest
	substituteCellString ThisComponent, sTest
	print "OUTPUT: ", sTest
End Sub

Sub substituteCellString(doc, sInput)
	l = instr(sInput, cTag1)
	r = instr(l +1, sInput, cTag2)
	if l = 0 OR r = 0 then exit Sub
	sAddr = mid(sInput, l +1, r - l -1)
	on error goto exitDocType
	oNames = doc.NamedRanges
	on error goto exitErr
	if oNames.hasByName(sAddr) then
		oRange = oNames.getByName(sAddr).getReferredCells()
	else
		oRange = ThisComponent.Sheets.getCellRangesByName(sAddr)(0)
	endif
	oCell = oRange.getCellByPosition(0, 0)
	sCell = oCell.getString()
	sInput = left(sInput, l -1) & sCell & mid(sInput, r +1)
exit Sub
exitDocType:
Msgbox "The current document is not a spreadsheet.",16 
Exit Sub
exitErr:
Msgbox "Can not substitute '"& sAddr &"'",16 
End Sub

Does this work for anybody?

P.S. The attached document has been created by following the above steps.

[Villeroy]

I want to put the date in the footer of a printed spreadsheet. But I would like to have the date format in the footer match the date format in the spreadsheet (yyyy-mm-dd). But the format in the footer seems to default to mm/dd/yyyy. How do I change it? I tried edit/Headers and Footers. I can change the font, but there seems to be nothing to change the actual date format.

Copy my code and call menu:Tools>Macros>Organize> Basic... Add a new module to "My Macros", library "Standard" and replace the new module's default code (sub main...end sub) with my copied code. You may also add the new module to your document so the document carries its own copy of the code.

IF the code is saved within the document, save the new document in a trusted directory according to menu:Tools>Options>Security>Macro Security. Do not lower the security level! Just add some directorie(s) to the list of trusted locations where you can store documents with embedded macros.
IF the code is saved under "My Macros", the macro will work with all your spreadsheet documents on this machine and for the currently logged in user.

menu:Tools>Customize... tab "Events". Assign the macro to the "Print Docment" event. It will be triggered before actually printing so the footer will be updated just in time. Glitch: The macro won't be triggered by mere PDF export. But a print-out to a virtual PDF printer or a print-to-file will do the trick (see print dialog).

Format a cell as ISO date and enter either =NOW() or =TODAY(). The cell may be anywhere, even on a hidden row, column or hidden sheet. You may give a name to that cell by simply entering a name into the name box on the left end of the formula bar while the cell has the focus.

Call menu:File>Properties, tab "Custom Properties" and add one custom property Default.RFL, Default.RFC or Default.RFR (left, center, right position). This will apply to all even and odd pages of page style "Default" unless you uncheck the "same content" option in the page format dialog (tab "Footer"). Set the value of your property to the cell address in curlies like {SheetX.A1} or {Name_of_Cell} if you named it.

[YODA]

The US Government tried converting to Metric as an experiment in several states back in the 1970s . They quit because a lot of people were getting off speeding Tickets by saying "I was doing the speed limit your Honor 65 Kmh". The radar guns did not do Metric and said they were speeding. After that they ended the experiment real fast.

[Krall]

I tried the macro above. I haven't fussed with macros in a long time, so it took me a while to install it. It still did not print the footer date as YYYY-MM-DD. I spent a good amount of time on this. In the end, I gave up. I will accept what OO spreadsheet gives me. At least, in OO text documents I can format the footer as I wish (and, frankly, that is the more important case). However, it would be nice if a future version of OO would allow formatting of spreadsheet footers.
Thanks to those who tried to solve my problem.

[Villeroy]

I spent a lot more time on this.
OpenOffice is an almost dead project. The successor is https://www.libreoffice.org/ many years ahead. The spreadsheet headers and footers are still the same, though.

Open my test document.
Call menu:File>Properties... tab "Custom Properties". There you see all header and footer entries I made for the Default page style.
menu:Tools>Macros>Organize>Basic shows the module embedded in my document's library "Standard". Hit the organize button and Ctrl+Drag&Drop the module to your Standard lib below "My Macros". Ctrl+Drag&Drop copies the module, Drag&Drop would move it.
WIth my test document active, open menu:Tools>Customize... tab "Events" and see how I assigned 2 save events which trigger my macro. They point to the copy that is embedded in my document. The headers and footers having an entry in the custom properties always update when the document is saved or saved under another name. You may want to use the print event for your own document. Play with that dialog, point it to the other copy in "My Macros". At any time you can quit the document without saving or download another copy from here.