Macro: EDATE in setFormula goes lowercase

Hello everyone,

I’m making a macro to set a formula in a cell with EDATE in the middle, one of the formulas that I need is:

but the result is making “EDATE” lowercase and breaking the cell data:

I have others cells in this sheet that use EDATE and all the others functions stay uppercase after running the macro, except EDATE that goes lowercase… Is there any way to force it keep uppercase?

This is the code that I’m using for the example above:


In any such cases, when you see .setFormula failing, it helps to test MsgBox(.getFormula(...)) on a cell formula that you created manually in UI and that works.

While EDATE is an ODF standard function, you may have to call it from Analytics in LO. Try:

in your string instead of just EDATE.


I know that might seem crazy, but a nice, pretty EDATE should pop up in the target cell, just like you want.

Your formula also worked joshua4, thanks.

But karolus’s solution is easier to remember, just change setFormula for FormulaLocal and remove the parenthesis.

Probably there is other functions that have the same macro problem with lowercase, karolus’s code worked without need to call something else, maybe FormulaLocal is the best fix this kind of problem.

FormulaLocal sounds fine if it works. However, notice that it isn’t a problem with lower case, nor parentheses.

  1. The lower case shows up because Calc does not recognize the symbol EDATE when entered via the method setFormula(). This is because setFormula is not designed (deliberately or not) to look at symbols, apparently. FormulaLocal, however, is, since LO does recognize the symbol EDATE when entered via the property FormulaLocal.
  2. A property is set using equality semantics, cell.FormulaLocal = "=A5". A method takes a value as a parameter, cell.setFormula("=A5").
1 Like
sDESPESAS.getCellByPosition(7,firstRowD-1).FormulaLocal = "=IF($F" & firstRowD &">TODAY();"""";EDATE($G"&(firstRowD)&";$E"&(firstRowD)&"))"

to avoid all this quoting- and concatenation-mess just use python with:

sDESPESAS.getCellByPosition(7,firstRowD-1).FormulaLocal = f"""=IF($F{firstRowD}>TODAY();"";EDATE($G{firstRowD};$E{firstRowD}))"""

It worked flawless, thank you karolus.

And about quoting and concatenation, I totally agree with you, a complete mess.

Please note that using FormulaLocal looks like an ugly hack. The code written this way is destined to fail on anything except current locale. Change to a locale with localized function names, or with different decimal separators, and it breaks.

The function should be accepted using Formula, since it’s standard ODF; IMO it simply deserves a bug report (and eventual fix, preserving old function as a compatibility alias). Maybe @erAck has a clarification…

Not really, would need debugging. For which a bug report with a proper sample would be welcome. Though I remember vaguely having heard such thing but don’t recall whether it was here on Ask or in a bug.

Update 2022-08-03T18:11+02:00
Problem is the formula API predates ODF (with OpenFormula ODFF) and closely follows the old syntax (PODF for Pre-ODF) where all AddIn function names are written/read by their programmatic name, hence the* names. Would need tweaking to at least accept the English names if known.

Filed as tdf#150253.

1 Like

EDATE is an add-in function. The add-in is shipped with this office suite. All these functions are not part of the ODF standard. The full name of EDATE as reported by getFormula is

EDATE is obsolete. The DATE function can calculate dates by calculated year, month and day numbers.;B1)
is equivalent to
calculating the date B1 months ahead of A1.

@joshua4 mentioned the correct fact that it is part of ODF.

Additionally, your formula id not equivalent to EDATE, since it doesn’t account for the different days in the end month, that could need to adjust 31th to, say, 30th or 28th.
And also DATE is not necessarily able to handle months outside of 1…12 correctly; standard requires only the mentioned range, and only permits implementations to do what they want with the values outside of the constraints - so that formula would be non-standard.


The closest relative is Basic DateAdd function (with “m” as the first parameter). :slightly_smiling_face:

My problem with this code is exactly with the 29th, 30th and 31th day of the month, as mikekaganski wrote.

I’m using this formula to track monthly payments and I need to have at least one payment per month.

A1: 12/30/21
EDATE(A1;2) → Result: 02/28/22
DATE(YEAR(A1);MONTH(A1)+2;DAY(A1)) → Result: 03/02/22 (Skipped February)

I can use IF inside to solve this problem, but I would have a long formula, EDATE is short and simple.

Well, then use something like