Use date formula for calc header

[foxylady337]

I would like to print today’s date as a header on each sheet of a bookkeeping spreadsheet unless the date is after the last day of the financial year; in that case I want the last date of the financial year.

For example, if the financial year runs from 1st July 2020 to 30th June 2021, and I’m preparing a report on the 15th of June, I’d want “as at 15th June 2021” to appear in the header. If I’m preparing it today (8th July 2021) or any other day after 30th June 2021, “as at 30th June 2021” should appear in the header.

[Lupp]

Headers and Footers in PageStyles for spreadshjeets are extremely poor. Using the standard fields, you can’t even apply reasonable number formats, not to speak of conditional values.
To get content of the kind you want into a header you would need (not exactly simple) user cosde.
I would suggest you only use a header if you need page counting. That’s the field for which no simple substitute exists.
Everything else: Calculate the wanted content in cells (may be hidden later), and reference these offside cells by cells of the very first row of your sheet in every column where a new page is starting or in every second or third… column generally. Set the first row to be repeated on every print page.
Extremely simplified code inerting a date like described into the headers follows. The routine must be called before the printing is started. Assumed is that there is no additional conetnt in the center ranges of the headers.

Sub insertConditionalDateIntoCenterHeaderLeftAndRight( _ 
       Optional pPageStyleName As String, _
       Optional pVariableDate, _
       Optional pMaxDate)
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
If IsMissing(pPageStyleName) Then pPageStyleName = "Default"
If IsMissing(pVariableDate)  Then pVariableDate  = fa.callFunction("TODAY", Array())
If IsMissing(pMaxDate)       Then pMaxDate       = fa.callFunction("DATE",  Array(Year(pVariableDate), 10, 31))
dateValToPrint = IIf(pVariableDate<pMaxDate, pVariableDate, pMaxDate)
dateToPrint = Format(dateValToPrint, "YYYY-MM-DD")
pSts = ThisComponent.StyleFamilies.getByName("PageStyles")
pSt = pSts.getByName("Default")
pStLH = pSt.LeftPageHeaderContent REM Deviation indispensable!
pStRH = pSt.LeftPageHeaderContent
pStLH.CenterText.Text.String = dateToPrint
pStRH.CenterText.Text.String = dateToPrint
pSt.LeftPageHeaderContent = pSTLH
pSt.RightPageHeaderContent = pSTRH
End Sub