Ask Your Question

When using the mm/dd format, why does calc add the year?

asked 2018-03-14 07:09:47 +0100

bblack4jc gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-03-14 08:08:58 +0100

Kruno gravatar image

updated 2018-03-14 20:43:57 +0100

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:

  1. pick a arbitrary year and enter your dates as dd/mm/yyyy and then format cell to only show dd/mm (in case you keep actual years in separate column, or you don't keep years at all)
  2. you could enter dd in cell column A and mm in 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 DataSort, 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 <colc> holding a year): =DATE(<colC>;<colB>;<colA>).

Will that work for you?


File datesWithDummyYear.ods shows what I meant under 1.

edit flag offensive delete link more


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.

bblack4jc gravatar imagebblack4jc ( 2018-03-14 08:30:21 +0100 )edit

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.

Kruno gravatar imageKruno ( 2018-03-14 20:46:58 +0100 )edit

answered 2018-03-14 17:44:54 +0100

Lupp gravatar image

updated 2018-03-14 17:45:48 +0100

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.

edit flag offensive delete link more


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.

Kruno gravatar imageKruno ( 2018-03-14 20:40:33 +0100 )edit

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.

Lupp gravatar imageLupp ( 2018-03-14 22:45:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-14 07:09:47 +0100

Seen: 49 times

Last updated: Mar 14 '18