OpenOffice Calc Tips

We can use an array formula to sum up only the positive numbers in a given range.

=SUM(IF(A1:A10>=0;A1:A10;0))

An array formula is defined by hitting SHIFT-CTRL-Enter instead of Enter for a normal (scalar) formula.

In documentation and texts, an array formula is indicated by ‘curly braces’ as shown below

{=SUM(IF(A1:A10>=0;A1:A10;0))}

However, the curly braces should not be included when entering the formula on the spreadsheet.

Harlan Grove added some comments regarding the Excel DATEDIF() function, that I feel deserve an article of their own…

---

Excellent example of POOR DESIGN in OOo Calc.

YEARS(EarlierDate;LaterDate;)
MONTHS(EarlierDate;LaterDate;)

but

DAYS(LaterDate;EarlierDate) [and no argument]

Maybe this was the fault of the original StarDivision programmers, but it’s still stupid, and it makes a bad impression for OOo Calc. Maybe it looks like this because the FAR SIMPLER equivalent formula

=LaterDate-EarlierDate

puts LaterDate before EarlierDate.

The only excuse for not fixing this is backwards compatibility: extant design errors must remain so that formulas adapted to those design errors continue to work.

Anyway, the more useful DATEDIF characteristics are YM, YD and MD. Those are more difficult to manage in OOo Calc. Actually, YM is simple.

=MOD(YEARS(EarlierDate;LaterDate;0);12)

YD takes more work.

=DATE(YEAR(EarlierDate)+(TEXT(EarlierDate;”mmdd”)>TEXT(LaterDate;”mmdd”));
MONTH(LaterDate);DAY(LaterDate))-EarlierDate

MD is more difficult.

=DATE(YEAR(EarlierDate);
MONTH(EarlierDate)+(DAY(EarlierDate)>DAY(LaterDate));
DAY(LaterDate))-$A3

is arguably more accurate, but it doesn’t produce the same results as Excel. Excel, for good or ill, NEVER returns anything higher than 29 for DATEDIF(EarlierDate,LaterDate,”MD”) while it can produce screwy results for 5 or fewer. Excel’s DATEDIF(EarlierDate,LaterDate,”MD”) may produce results consistent with DAYS360, but I’ll leave that to someone else to check.

---

Thanks Harlan for taking the time to comment on this topic!

I recently came across a blog in Indonesian - which had useful pointers related to OpenOffice alternatives to the Excel DATEDIF() function. Here is the link: http://maseko.com/

The DATEDIF() function is a strange beast in Excel - so much so that it isn’t even implemented in OpenOffice Calc.

The syntax for DATEDIF is as follows:

=DATEDIF(Date1,Date2,Interval)

Where

Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.

There are sixdifferent interval codes in Excel DATEDIF() and we will cover them all with alternatives in OpenOffice Calc.

For example, to calculate someones age (in years) in Excel, you would use =DATEDIF(A1,NOW(),”Y”)

In OpenOffice CALC, we can use =YEARS(A1;NOW();0)

For calculating number of months in Excel, we use =DATEDIF( A1,NOW(),”M”)

In OpenOffice CALC, we use = MONTHS(A1;NOW();0)

For calculating number of days using DATEDIF, we use  =DATEDIF( A1,NOW(),”Y”)

In Openoffice CALC, we can use.. =INT(DAYS(now();A1))

Please bear with me while I relocate my Open Office tips to WordPress.

For an full index of articles in the old blog, see http://www.richhillsoftware.com

Right now, I’m cutting pasting articles from old blog to new. Then I will do cleanup. At the same time I’ll start writing new tips for Open Office Calc.