How to make DATEADD (or equivalent) work in LibreOffice Calc?

[LibreOffice Calc Version (Build ID: 360m1(Build:2)) on Ubuntu 12.10 64 bit.]

What I want to do is very simple: take a date from one cell, say B1, a number of months from a second cell, say B2, and add them together to yield a new date in a third cell, say B3.

After some searching I found this web page, documenting the existence of a DATEADD function: DateAdd Function [Runtime] - LibreOffice Help

Trying the obvious didn’t work: entering

=dateadd("m", B2, B1)

in B3 gave


After some experimentation I guessed that the DATEADD function can’t be entered in a spreadsheet cell and is only available to Basic (WTF?). Anyway, I then made a simple wrapper around it in a Basic module:

Function WrapDateAdd(add as String, count as Integer, date as Date) as Date
WrapDateAdd = DateAdd(add, count, date)
End Function

This time it almost worked, in that entering

=wrapdateadd("m", B2, B1)

in B3 gave


The problem then was that the value in B3 didn’t seem to act as a proper date; it didn’t respond to date formatting requests, i.e. it still displayed in that ambiguous “dd/mm/yyyy” (or is it “mm/dd/yyyy”?) format even if I asked for, say, “yyyy-mm-dd” or “9 April 2013”.

An even more troublesome side effect is that the value in B3 does not behave like a date in comparisons, which dramatically affects the functionality of the rest of the spreadsheet that depends on the computed value. For example, a formula like =B3<B10 (does the computed date come before the date in B10?) will return FALSE no matter what, whether B10 contains a 2000 date or a 2020 date.

The documentation page above says that DateAdd returns “A Variant containing a date”, so I can’t figure out why it doesn’t behave like a proper date.

If anyone can suggest a working solution this will be much appreciated.

@mariosv: indeed so, EDATE is what I needed in this case, and fixes my immediate problem, so I marked the question as answered; but what if I had wanted to add days, instead of months? I’d still like to know why the result of WrapDateAdd doesn’t behave like a date (if you can figure it out). Many thanks though!

I don’t know why your function does not behave as a date, but there is a work around. Have your function return an integer, then format the cell as a date.

@w_whalley: maybe I’m missing something but, if I try that, I get a dialog box with “Inadmissible value or data type. Overflow”.

I think EDATE(date;months) is the function you’re looking for