I am doing a spreadsheet where I only need the month and date to show: mm/dd. When I go to do a sort, it does not sort properly because the birthdays are entered in different years due to libre office adding an automatic year on the end. Is there any fix for this? I do not have the actual year for the people on my spreadsheet.
It’s date acceptance pattern. So when you enter dd/mm it assumes you want that date for this year and you can’t get rid of it unless you use different pattern.
It’s not clear are you keep track of actual years in separate column or not and I don’t know if you need the actual birthday years or not but here’s what you can do:
- pick a arbitrary year and enter your dates as
dd/mm/yyyyand then format cell to only showdd/mm(in case you keep actual years in separate column, or you don’t keep years at all) - you could enter
ddin cell column A andmmin column B as plain numbers and then use sort to sort by two keys: select columns A and B (and C if you keep track of actual years in different column) then go Data → Sort, choose column B as sort key 1 (month) and column A as sort key 2 (day).
Second option is much better. You can still make dates of those two columns with DATE function if you need to process dates (assuming holding a year): =DATE(<colC>;<colB>;<colA>).
Will that work for you?
[UPDATE]
File datesWithDummyYear.ods shows what I meant under 1.
Thank you for posting to my question. The second option would work. It is just frustrating that Libre Office Calc doesn’t have a better way to do that. When it shows the mm/dd format, it should just use that. It’s a pain to have to go back and redo a huge worksheet. But, hey. It’s free so I shouldn’t complain too much. than you, Kruno.
Maybe you don’t need to design or re-structure the whole spreadsheet. If you go with first solution, you can append actual years to the end of the spreadsheet and just re-enter the dates. No need for doing whole table from scratch. Accept answer which helped you to wrap this up.
There isn’t a thing like a “date-of-year” in the world of spreadsheets, and that has nothing to do with whether the software is free or paid for. There are dates, and each date is expected to determine one single day in history. Thus a date is represented internally by the number of full days elapsed since a specific day zero, 00:00:00. The common day zero for spreadsheets is 1899-12-30. Earlier dates are represented by negative numbers. (Today: 43173.)
Therefor a spreadsheet getting something entered looking roughly like a date needs to do two things: Firstly decide if actually a date should be “recognised”, and secondly decide which year should be used if explicit entry was omitted for “abbreviation”. The only halfway acceptable automatic completion I can think of is “the current year”.
An attempt to introduce something like a cyclic DateOfYear (similar to TimeOfDay) would fail because of the leap-year problem. For the same reason the “arbitrary year” mentioned under 1. by @Kruno won’t do. It must be a leap-year.
I personally would also never suggest to suppress the year if a date was entered. One day you will notice that ‘Calc’ comes from “calculate”, and you will ask the sheet how old Pete is or how many days are left till Margot’s next birthday. It’s hard to design and debug reliable formulae for calculations with dates presented in a lying format. Even something forced to be text would be better then.
BTW: A thing like 07/08 may be interpreted as whatever. You may read it as “the eighth day of july of any year”, and a UK citizen might read it in a similar way, but meaning “the seventh day in august of any year”. Someone else may be accustomed to understand it as a range of sizes or as room 08 on the seventh storey. A spreadsheet cannot know this kind of context reliably. Thus it shouldn’t accept such entries at all - except as text. Bad reasons led to a different behaviour. The one recommendable format for dates in spreadsheets is YYYY-MM-DD. Any “localised” formats are just hokum.
What do you mean by ‘won’t work’. When you say 'The only halfway acceptable automatic completion I can think of is “the current year”. ’ than that’s exactly what I had in mind, but instead using current year as it can mess up sorting next year - instead of current year use, let say ‘2000’. I attached example to my answer so I would like you to take a look and test if I got something wrong. Put this solution on first place as it somehow simpler then second solution. Thanks.
To use a (fake) arbitrary year is not a good idea from my point of view generally for reasons I tried to explain in my answer. I’m afraid I cannot do better due to my poor English.
Using year 1900 specifically (1900-01-01 as a surrogate for zero) would not work as expected for 02/29 because 1900 not was a leap-year. Most years would be bad choice for that reason. To change arbitrarily would spoil calculations.
Second workaround and avoiding date formats completely is ok so far.
Kruno, I don’t know how to put that solution in first place. To sum up:
I used Kruno’s first solutions to change all the years to the same year.
I did this because there was no need to know how old someone is or how many years they have been married. The dates are taken from the spreadsheet and put into a newsletter to show the members who have birthdays and anniversaries. Hope this help to clear up everything.
I ended up using Kruno’s first suggestion. Let me explain further what I was trying to do. I have a spreadsheet that includeds a column for birthdays. That column I wanted to show only the month and day = mm/dd. However, when I would enter for example; 09/05, it would show it as 09/05, but the cell when clicked on, showed 09/05/2019 or whatever year it was. When it added the current year, I could not sort the column only by month, because some were entered in different years. Does that make sense?
Example: 09/05 entered in 2017 That cell when clicked on showed: 09/05/2017 When not clicked on showed: 09/05
If I entered a birthday in the year 2018, Example: 09/19 when you click on the actual cell it showed: 09/19/2018. When not clicked on, the cell would show only 09/19.
So, when I go to sort for all September birthdays, it would show all 2017 birthdays, then all 2018 birthdays, sorting by year first, then by month.
I ended up inputing all birthdays with the same year. Example: 2016, leaving the formatting as mm/dd. I put a note at the top of the spreadsheet to input all birthdays and anniversaries with the year 2016.
I was wishing that Libre office would not have automatically input the current year and just input the mm/dd.
Thanks to all who helped on this.
Well - I’d use the correct birth dates instead (like 1956-02-15) for everyone in the list - which would make it useful to calculate e.g. ages - and use an auxiliary column for sorting by months, with a formula like =MONTH(<BirthdayCell>).
The column could be hidden if required; it might have a caption to help sorting (so it would be visible in the Sort dialog to allow select something like “BMonth” as sorting column).
And in case I don’t know the exact year of a birthday I want to be able to watch for the anniversaries, I would use a default leap year clearly unrealistic for my relatives and friends, 1604 e.g. thus making a 29th of february acceptable. The watching for the anniversaries in this specific case requires special attention, however.
BTW: The formats with a single slash should finally be reserved for fractions without any exception. Using something like 10/12 as a DateOfYear without disambiguating it should be penal. A somehow acceptable format for (intended) DateOfYear would be "yyyy"-MM-DD.
Just make sure you watch out for possible problems Lupp explained in his answer. I would personally go with entering numbers - not dates - in three columns and then sorting all three columns only by month column. After that, you can simply get date using DATE() function. Problem regarding last part of your answer is that you can not have date if you do not have year. 09/05 is not a date, it’s a string of character. You can’t interpret it as date without a year. Having date in column A, month in column B and actual year in column C is something that allows you to control every aspect of your table.
I did not need to know the year or calculate the age. Most people did not even give a birth year when filling out the form that I took this information from. Similarly, I did not need to know how many years they have been married, only the month and date.
It is just a simple spreadsheet to keep track of members and their basic information. I keep it in the mm/dd format so that I can sort on a certain month and find all of the members with birthdays for that month.
The main problem is that in LO writer, it will not accept the mm/dd format. It needs the year as well. As Kruno pointed out, I had to put in the same year otherwise it would not sort properly.
This questions has been answered by Kruno and there is no need for further discussion.