Ignoring past dates

[Tazz]

In 1-2-3, I used the following expression, to enable me to adjust a monthly payment during the year without affecting earlier entries.

@IF(@DATEVALUE(B15)>@TODAY,D4,D15)

In that case DATEVALUE and TODAY simply returned a global number that represented the day. I can't find an equivalent way of testing for an ealier date. Has anyone a tip for achieving this please.

TIA

[Villeroy]

=IF(B15>TODAY();D4;D15)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Tazz]

Thanks for that. It didn't occur to me that OO could translate the date. Doh !!

[Tazz]

Hi - I'm still stuck, despite now understanding the Date funtion a little better.

What I would like to be able to do, is simultaneously update each of 12 cells, one for each month of the year. Then subsequently, only those beyond the current month should be updated, with earlier months values being retained.

Anyone want to take on the challenge for me ? Please.

[noranthon]

No doubt someone will know what you mean but I, for one, do not. What do you have in the cells and what do you want?
_________________
search forum by month

[Villeroy]

DATE("2000-12-31") tries to convert a string to number and displays the number as date if the cell is not formatted otherwise.
TODAY() returns today's number and displays the number as date if the cell is not formatted otherwise. I believe 1-2-3 should work with this: @IF(B15>@TODAY,D4,D15).
There are no "date-values" in spreadsheets. Dates are numbers, counting days since null-date. The integer part of the number is displayed as years, months and days. The fractional part of the number is displayed as hours:minutes:seconds. Default null-date is 1899-12-30 in StarOffice, 1900-01-01 in Excel and 1904-01-01 in Lotus1-2-3 (not shure about the last one). See Tools>Options>Calc>Calculation. Negative numbers represent dates before null-date back until gregorian reform (there is a gap of non-dates between 1582-10-04 and 1582-10-15).
=B1-A1 --> Days between A1 and B1
=(B1-A1)*24 --> Hours between A1 and B1
All those helping date/time- functions are either converting strings to numbers or handling unregular units of time, such as different count of days in months and years.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Tazz]

Thanks very much for that detailed explanation - Very helpful.

I'm sorry my explantions wasn't clear enough, and there was me thinking it was spot on.

I'll see how I get on before trying to improve on my previous effort. Thanks all.

[Tazz]

Right forget previous inadequate descriptions, though I have learnt how dates work now.

I want 1 cell at the top with a monthly deposit amount, say 250.

Then 12 vertical cells representing Jan - Dec, which would initially, simply be a straight copy of the monthly deposit.

Then say after 6 months, I want to be able to change the monthly amount, but of course it must only alter the last six months. i.e. August - December.

1-2-3 allowed you to include the target cell as a outcome of an IF statement, meaning that the cell remained unchanged. OOCalc complains that this is a circular reference Error 522

TIA

[Nylo]

Don't make the 12 cells copy the initial "default" cell. Make only the first of the 12 cells, i.e. January, copy the default value. Then make the others copy the cell inmediately before. This way February copies January, March copies February and so on. If you want the ammount to change starting in, say, April, edit the Formula in April substituing the copying of March with the new ammount. May, June, July, etc will be copying then April's new value while January, February and March keep the old one.

See u.