2009/10/15 Dotan Cohen <[email protected]>
> I am having a hard time with the now() function in calc. I have a
> column with the date in yyyy-mm-dd format. I need another column that
> shows the difference between now() and that column, and if the
> difference is negative (that means that the date has passed) it will
> show the text "expired". I have tried playing with the if function and
> I have gone through all the possibly related function in the official
> list [1] however this seemingly-simple exercise eludes me. Maybe I
> need more coffee?
>
> [1]
> http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_Functions_listed_by_category
>
>
Assuming the dates are in column A,
=IF(DAYS(NOW();A1)<0;"Expired";"Current")
or
=IF(INT(NOW()-A1)<0;"Expired";"Current")
For some reason which I don't understand, the formula "=now()-A1" is
displayed as a date rather than as a number, even when the result column is
*not* formatted as Date. Using the INT() function fixes this, as does the
first scheme.
If you want the number of days instead of the word "Current", simply replace
it with the initial expression
DAYS(NOW();A1)
or
INT(NOW()-A1)
Perhaps someone would explain why subtracting one date from another results
in a date instead of a number. Oh, of course, would it be a number of days,
hours, decimal fractions of a year, milliseconds or what? But not a date,
surely ???
Oh again. Calc's Help under "Calculating with Dates and Times" covers this
problem. It says you need to format the result as Number (which it already
is) but with something like "-1,234" instead of "General" because using the
latter causes the value to be interpreted as a date. Seems to work :-)
So, if you format your result column as Number and -1,234 *before* entering
any formulae, then
=IF(NOW()-A1<0;"Expired";NOW()-A1)
works as expected.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
