Date format won't change

[stankoprowski]

I have a small CALC contact list and I'm trying to sort by date. I've tried right-click on the column/numbers/date/12-31 to give a date without year. If I select a date like 12-31, in the edit box(?) I get a different format, 12/31/2014 which I can't change. For example when I try to sort dates the third month is split into 3 groups separated by other dates. If I select the first group the edit box(?) shows 03/XX/2012. The second group 03/XX/2014 and the last 03/XX/2015. I believe the year dates are the year the row was created and are affecting the sort. In my file the years are in a separate column

What am I doing wrong? How do I get the 12-31 format in both the column and the edit box(?) so I can do the sort that I want?

Stan

[Zizi64]

Formatting a datetime value not eliminate the 'year' part, but just hide it only.
You need convert the values by some furmula for "normalize" (eliminate) some part of the datetime value.
The you will able sort them as you want.

The 'Datetime' is a numeric value. The integer part represents the 'year', 'month', 'day' parts (it means: the number of days elapsed since the base date), and the fraction part represents the rest 'hours', 'minutes', 'seconds'.

[keme]

Like Zizi64 said, a date value will always have a "year component".
Your suspicion is correct: If you don't specify the year when entering a date, Calc will assume current year. Calc does not have any "input mask". Formatting only applies to display.
(Exceptions are fraction formats and text formatting, which hints Calc towards how the input be interpreted, but still do not provide an input mask as such.)

To avoid Calc's assumptions when you enter day/month, you need to avoid the datevalue data type. Mostly it is recommeded to format your input cells as text for this purpose. If you need the proper datevalue (e.g. for sorting), you use a formula to interpret the entered digits as a date. String extraction functions (LEFT/MID/RIGHT), VALUE and DATE may be useful for that.

To sort all dates within a year (s you would to create a birthday list), you use same year for all in the DATE() function, just picking the day and month from entered data.

Hope this makes some sense...

[RusselB]

The edit box will always show the recognized date, usually in one of two formats. The first is as you have already seen, the second is as an integer that represents the number of days since the system base date.
One option is to use a couple of helper columns. If your dates are in column A, enter

Code: Select all

=month(A1)

in column B and

Code: Select all

=day(A1)

in column C
Then set your data range to include columns A-C, and sort with column B as your primary key and column C as your secondary key.
This will sort your data ignoring the year portion, thus Feb 10 1902 will have the same sort value (for lack of a better term) as Feb 10 2102
Normally when dates are being sorted, the year is wanted to be part of the determination for the sort, but you're requesting otherwise.

[stankoprowski]

Thanks all. I changed the date to text and while it's not pure it does what I needed. I had to re-enter the data because XX-XX changed to a 5 digit number.

Stan

[RusselB]

The only difference between the XX-XX format and the 5 digit number is the format. To Calc they are identical

[stankoprowski]

Thanks I figured something like that. Looks like a Star Date or something.

Stan

[RusselB]

It's the number of days since the date specified in the settings as the system start date
For AOO that date is Dec 30 1899 (by default), StarCalc 1.0 uses Jan 1 1900, and some other spreadsheet programs use Jan 1 1904
If you enter the number 0 into a cell, then format it as a date you'll get one of those three.