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, _
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