Ask Your Question
0

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

asked 2013-04-09 21:00:31 +0200

lost gravatar image

updated 2015-10-29 16:19:01 +0200

Alex Kemp gravatar image

[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: https://help.libreoffice.org/Basic/DateAdd_Function_Runtime

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-29 16:19:16.557015

Comments

@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!

lost gravatar imagelost ( 2013-04-09 21:30:47 +0200 )edit

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 gravatar imagew_whalley ( 2013-04-09 22:45:07 +0200 )edit

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

lost gravatar imagelost ( 2013-04-10 16:26:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2013-04-09 21:18:41 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-04-09 21:00:31 +0200

Seen: 6,195 times

Last updated: Apr 09 '13