I type date in format MM.YY and I want that Calc interpret that as date LAST DAY OF MONTH.MM.YYYY. I want chnge e.g 01.2026 to 31.01.2026. It’s ever possible?
After some changes if I type MM.YY Calc change this in 01.MM.YYYY. I can’t force to change this.
Thank you!
Number formatting shows the same number in different ways. What you want is a different number.
The DATE function can calculate day numbers from 3 other numbers for year, month and day where each argument can be smaller than 1.
=DATE(YEAR(A1);MONTH(A1)+1;0) calculates the 0th day of next month from the day number in A1.
As already @Villeroy stated, a solution doing exactly what the questioner asked for does not exist.
However there are workarounds relying on user code.
The attached example contains document macros for the purpose (one of the workarounds, the others are only explained) and demonstrates the working.
Of course somebody actually wanting to use the workaround
should move the code to the user profile, and adapt the vnd-callers used by the HYPERLINK() formulas.
disask111398changeDatesToLastDayOfMonth.ods (45.8 KB)
The code uses the Calc function REGEX() only implemented in LibO V6.2 or higher.
Install this macro: https://forum.openoffice.org/en/forum/download/file.php?id=46813 and assign your own shortcut and/or toolbar icon to it.
Start your list with whatever date input and a date formula like =DATE(YEAR(A1);MONTH(A1)+1;0). Every time you call the macro, a new row will be inserted (or as many rows as cells are selected vertically) and any existing formulas will be copied from adjacent cells above.
it’s trikky:
set the day of a DATUM to 1, then add the month by +1, then add the day by –1:
25.12.1999 → 01.12.1999 → 01.01.2000 → 31.12.1999:
01_LO-CALC_DATUM-erster sowie letzter Tag im Monat eines Jahres_iN ARBEiT-v0001_095744.ods (84.5 KB)
The DATE function can calculate with zero and negative months and days. DATE(year(A1);month(A1)+1;0) with a December day in A1 calculates DATE(2024;13;0) and returns 2024-12-31.
@Villeroy
in LO v.7.2.7.2 (MacOS) is the variable (…;0) permissible but the helping assistent shows me for the reqired days only (…;1 to 31).