Question

Can anyone please tell me what's wrong with this formula?

%CALC{"$IF( $T(R1:C3) <= $FORMATGMTIME($TIMEADD($TIME(), 2, week), $day $mon $year), $T(R1:C1) $T(R1:C2) , END)"}%

Because I get this result:

ERROR: Illegal octal digit '8', at end of lineIllegal octal digit '8', at end of line

If I replace the FORMATGMTIME section with a date value the IF statement works correctly. Also if I have the FORMATGMTIME section outside of the IF statement it resolves to the expected date.

Environment

-- EmmaForrester - 24 Sep 2008

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

You're comparing apples and oranges: a date string with a serialized date.

At the time of this writing, this:

   * 1st term: %CALC{$TIME($T(R1:C3)) -> $T(R1:C3)}%
   * 2nd term: %CALC{$TIMEADD($TIME(), 2, week) -> $FORMATGMTIME($TIMEADD($TIME(), 2, week), $day $mon $year)}%

Returns this:

• 1st term: 1222153200 -> 23 Sep 2008
• 2nd term: 1224182478 -> 16 Oct 2008

And comparing like terms:

   1. %CALC{$IF($T(R1:C3) <= $FORMATGMTIME($TIMEADD($TIME(), 2, week), $day $mon $year), $T(R1:C1) $T(R1:C2), END)}%
   2. %CALC{$IF($TIME($T(R1:C3)) <= $TIMEADD($TIME(), 2, week), $T(R1:C1) $T(R1:C2), END)}%

Does this:

1. END [string comparison, gives wrong result]
2. Fred Blogs [serial date comparison, gives correct result]

> If I replace the FORMATGMTIME section with a date value the IF statement works correctly

That was probably a fluke. Notice in my example that "23 Sep 2008 <= 16 Oct 2008" is false, since that IF returned "END".

So comparing serial dates, and using FORMATGMTIME outside of the IF statement, is the way to go.

-- SeanCMorgan - 02 Oct 2008

Thank you very much for your help with this mistake of mine.

-- EmmaForrester - 03 Oct 2008