Using LibreOffice 24.8.7.2 (X86_64) on a Win 11 PC,
This spreadsheet has friends’ birthdates.
If I don’t know the Year of birth, I enter 1900.
I enter information only into the Blue cells, columns B and C.
When this was an MS Excel file, Columns A and D were automatically formatted, from Column C.
Now that it is an ODS file, I cannot get Columns A and D correct.
HOW to format those Pink cells correctly?
Thanks.
Mark5023
DateTest11.ods (20.2 KB)
Thanks for that fast reply, JohnSUN.
That works for column D when I put “=c9” in A9 and D9.
(it toggles between a Julian number, and MM/DD/YY).
In column A, though, I want only MM/DD. How to get that?
Mark the column, then use the context menu (right mouse button). Something like “format cells” (depending on your locale/language settings).
Chose a date format of your liking.
1 Like
OK, I think that sorta’ works, for individually added cells, Wanderer.
But when I look at A8, the Formula Bar shows '06/19
In A9, the Formula Bar shows =c9
It all works, but it’s inconsistent.
How to get column A and D cells to look at column C for the source?
I cannot see how A1 thru A8 got the info from column C.
Enter =C1 and format accordingly
Manually I suppose.
I suppose Column A is for sorting, I am not sure what column D is for.
Instead of manually entering Column A you could have a formula =DATE(2025,MONTH(C1),DAY(C1))
and you could sort on that.
You could instead of entering a fixed year of 2025, you could link it to cell A1. That cell could have a formula such as =TODAY() and be formatted as YYYY for clarity. The formula would then need to be =DATE(YEAR($A$1),MONTH(C2),DAY(C2))
DateTest123695EA.ods (23.7 KB)
I may have misunderstood your problem and the purpose of your table columns, but if I were to make a calendar like this, I would organize it something like this. Data is entered into the green cells, and the pink cells are recalculated automatically.
DateTest11.ods (24.3 KB)
@EarnestAl has already explained the main part of the calculations.
1 Like
The leading ’ signals: This is interpreted as string.
So formats for numbers are ignored.