Download it now!
Bug 31939 - FORMULA: command TODAY() not usable in automatic formula computing
Summary: FORMULA: command TODAY() not usable in automatic formula computing
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 Beta3
Hardware: All All
: medium major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:3.4 target:3.3.2
Keywords:
: 33580 35210 (view as bug list)
Depends on:
Blocks:
 
Reported: 2010-11-26 08:45 UTC by Joachim Wiedorn
Modified: 2011-03-12 06:04 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample document (8.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-11-27 00:54 UTC, Rainer Bielefeld Retired
Details
New test with IF function (11.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-12-27 12:26 UTC, GerardF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joachim Wiedorn 2010-11-26 08:45:56 UTC
First I have tested it in German with "HEUTE()", then also with TODAY(). I have uesd this command in a formula like this:

  =IF(D28>1;YEARFRAC(D28;TODAY();1);"")

If I open the file with this formula inside and add a date in the empty field D28, then the formula field do not compute/show any value.

If I write instead:

  =IF(D28>1;YEARFRAC(D28;F$3;1);"")

and have set the formula "=TODAY()" into F3 then computing in field D28 is o.k.
(this can be used as workaround).

I have found this error in Beta 3. (I have not tested Beta 2).

Thanks for all your work!
Comment 1 Rainer Bielefeld Retired 2010-11-27 00:53:11 UTC
[Reproducible] with "LibreOffice 3.3.0Beta3 - WIN XP DE [OOO330m9 (build 3.2.99.3)]"
Steps to reproduce: (I compared to OOo 3.1.1)

Step:                                                  
1. Open attached "sample11.ods" 
   LibO: 'A1'='A2'="X"
   OOo : 'A1'='A2'="X"
2. Insert "1.1.2010" into 'D28'
   Libo: 'A1'="X"     -   'A2'="110,01"  A1 value unexpected!
   OOo:  'A1'="0,9"   -   'A2'="110,01"  as expected
3. To heal unexpected LibO result, you have to doubleclick A1, 
   Insert a blank somewhere in the formula,  
   Backspace to delete the blank, 
   <Enter>
   Now Libo will show 0,9 or similar as expected
4. Modify date in D28 to "1.2." : 'A1'=0,82 as expected
5. Modify date in D28 to "1.3." : 'A1'=0,74 as expected
6. Modify date in D28 to current date : 'A1'=0 as expected
7. Modify value in D28 "0" : 'A1'='A2'="X" as expected
8. Restart from 4
   expected: 'A1'=0,82
   actual:   'A1' remains "X", you have to heal due to step 3 to get the
              correct value

The problem alternatively can be healed by saving, closing and reopening document in step 3.

OOo 3.1.1 never shows the incorrect "X"
OOo3.4-dev also works correct with "sample11.ods" 
 
If you insert"=today() into 'F3' 'A1' and 'A2' will show the same value as long as formula in 'aA1' works correctly, the reported problem remains.
Comment 2 Rainer Bielefeld Retired 2010-11-27 00:54:26 UTC
Created attachment 40598 [details]
Sample document

Pls. see comments Rainer Bielefeld how to reproduce the problem
Comment 3 GerardF 2010-11-27 07:05:56 UTC
> 3. To heal unexpected LibO result, you have to doubleclick A1, 
>    Insert a blank somewhere in the formula,  
>    Backspace to delete the blank, 
>    <Enter>
>    Now Libo will show 0,9 or similar as expected

Or press F9
Problem is the formula is not re-calculated.
Comment 4 GerardF 2010-12-27 12:26:55 UTC
Created attachment 41477 [details]
New test with IF function

It is not only a IF(TODAY()) problem.

When you used IF formula, and this formula contains "volative function" like NOW, TODAY, OFFSET, RAND, INDIRECT, OFFSET... formula is not recalculate.
You must press Shift+Ctrl+F9 keys.

Examples in the attachment.
Comment 5 Kohei Yoshida 2011-01-04 16:10:59 UTC
I'd like to take a look at this for the next minor release.
Comment 6 Kohei Yoshida 2011-01-27 08:21:33 UTC
*** Bug 33580 has been marked as a duplicate of this bug. ***
Comment 7 Kohei Yoshida 2011-03-02 18:00:26 UTC
Ok.  Just fixed this on master.  The reason why it would not re-calculate was because the IF function may skip the volatile function such as NOW() depending on the result of the evaluation.  So, for example, given

=IF(<condition>, TODAY(), "X")

when the condition was true, TODAY() would get evaluated and the cell would get marked volatile.  But if the condition was false, TODAY() would never get evaluated, and "X" is not a volatile token, hence the cell would get marked non-volatile.

My solution is to scan all tokens in the beginning of the calculation looking for a volatile token.  There may be a better way to do this than that, but at least this works.
Comment 8 Kohei Yoshida 2011-03-02 22:19:52 UTC
I put a slightly better fix that doesn't require re-scan of the token array.  It's better performance-wise.
Comment 9 Kohei Yoshida 2011-03-03 16:21:50 UTC
Backported the fix to 3.3.2.
Comment 10 Michel Rudelle 2011-03-12 03:55:41 UTC
Checked with 3.3.2 rc1 on Vista, it's ok, thank's very much !
Comment 11 GerardF 2011-03-12 06:04:57 UTC
*** Bug 35210 has been marked as a duplicate of this bug. ***