How to autogenerate a date in a libre calc

Question

I think this is easy but I am not finding solutions online. I have recurring bills and would simply like date values to change every month in their respective cells.

For example if cell A2 due date shows 12/24/2019 and it's now 12/25/2019, then I would like the cell A2 due date to now become 1/24/2020.

Then when it's 1/25/2020 the next date is 2/25/2020 etc.

Likewise, if it's an annual bill, and cell A2 due date is 12/24/2019 and it's now 12/25/2019 then cell A2 due date changes to 12/24/2020.

Is there a simple function for this?

Answer 1

If you have e.g. 2019-12-03 in A1 (that is 'yesterday' as I write this)
then a cell containing the formula below will show the date one month later.
Edit A1 to contain a date AFTER or EQUAL to the current date and the formula will show an empty cell.

=IF(TODAY()>A1;DATE(YEAR(A1);MONTH(A1)+1;DAY(A1));"")

For "an annual bill" the +1 would need to be just after YEAR() instead.

---

This formula (a):
=DATE(YEAR(TODAY());MONTH(TODAY());25) ... will display the 25:th of the current month, until the month changes - e.g. for a monthly updated due date.

Similarly (b):
=DATE(YEAR(TODAY());1;25)
will show January 25th the current year (i.e. change as the year changes).

if you replace A1 with $A$100 above, to get this (c):
=IF(TODAY()>$A$100;DATE(YEAR($A$100);MONTH($A$100)+1;DAY($A$100));"")
and place either of a) and b) in A100, you will see each and any cell containing c) update as you either change A100 manually, or the formula there gives a new value (as shown above at a or b).

Or maybe (d) to show EITHER the 25th of current month while current date is before that, OR 25th of NEXT month if current date is past the 25th.
=IF( DAY(TODAY()) <25;
DATE( YEAR(TODAY()); MONTH(TODAY()) ; 25);
DATE( YEAR(TODAY()); MONTH(TODAY())+1; 25)
)

---

Please use an unambiguous date format, not something that is prone to be guessing game depending on the actual date.