I have a date in cell A1.
I want return a date to cell A2 which is x years earlier.
What formula should I enter into cell A2?
=EDATE(A1;-12*1)
=EDATE(A1;-12* x years )
2 Likes
Thanks for all sugesstions and comments.
The support provided by AskLibreOffice is superb.
1 Like
Assuming x is an integer value in Cell X1:
=DATE(YEAR(A1)-X1;MONTH(A1);DAY(A1))
If the date in A1 gives a leap day, and X1 years earlyer is none, the formula I suggested will handle the conflict differently from what you get by the suggestion posted by @LeroyG.
In the ordinary case I would prefer the DATE() function.
It should be noted that the result of the formula
=DATE(2024-1;2;29)
equals 2023-03-01 (in Calc and Excel).
| one year earlier | ||
|---|---|---|
| origin | with EDATE | with DATE |
| 2024-02-26 | 2023-02-26 | 2023-02-26 |
| 2024-02-27 | 2023-02-27 | 2023-02-27 |
| 2024-02-28 | 2023-02-28 | 2023-02-28 |
| 2024-02-29 | 2023-02-28 | 2023-03-01 |
| 2024-03-01 | 2023-03-01 | 2023-03-01 |
| 2024-03-02 | 2023-03-02 | 2023-03-02 |
| 2024-03-03 | 2023-03-03 | 2023-03-03 |
I cannot judge which of both is more correct, but EDATE seems shorter?!
2 Likes
There can’t be a “correct” solution. Users need to decide what’s more appropriate in the specific case.
- One doubtable
+forDATE(): It applies automatic date formatting to the cell where it was called. - A second
+forDATE(): I once registered a motorbike on 2012-02-29 hoping I would have to pay yearly taxes only every 4th year. In fact the payday was shifted to YYYY-03-01. - In other cases of paydays a shift concerning the month will be unwelcome. But in such cases I would assume the
EOMONTH()might be needed - and details may depend on the weekday in the special case.
You can’t generally avoid a mess when using a “lunisolar”.