[LibreOffice Calc Version 3.6.2.2 (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
#NAME?
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
09/06/2013
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.