[Solved] Change value of cell dependent on day and month

[endbrown]

I am trying to create a cell formula as part of a budget spreadsheet where the displayed value is dependent on the month of the year, as well as the day of the month. I have a formula that works on Excel but I am unable to make it work on OpenOffice. Here is the formula:

=IF(MONTH(TODAY())=9,(30-DAY(TODAY()))*100, IF(MONTH(TODAY()) < 9,0,3000))

With this formula, the cell would display "$0.00" for January - August and "$3,000.00" for October - December. For the month of September, however, it will display a value based on the day of the month that decrements by "$100.00" each day. I would have similar formulas for the other 11 months, with appropriate changes to reflect the month and how many days are in the month.

If anyone can provide me with a formula that will work in OpenOffice I would greatly appreciate it.

Thank you.

[RusselB]

I suspect that your Excel formula would work in OpenOffice by simply changing the commas to semi-colons. Excel uses commas as parameter separators, but OpenOffice Calc uses semi-colons.
As a quick suggestion as to an alternative formula, based on what I think you are trying to get, try

Code: Select all

=IF(MONTH(TODAY())=9;(DAY(EOMONTH(TODAY();0))-DAY(TODAY()))*100; IF(MONTH(TODAY()) < 9;0;3000))

The EOMONTH function, with the today() and 0 parameters, and wrapped in the DAY function, returns the last day of the month, so no need to worry about how many days there are in the month, and even covers leap years.
As to how you set that up for the other months, that is something you'll have to figure out.