MONTH() and YEAR() functions behave differently in scalc 3.5.7 and 4.x

users
#1

I think this is a feature.

The definition of the MONTH() and YEAR() functions require their single argument to be a date value - that is, a numerical value which can be formatted as a date. I think in giving them a text argument, you are relying on the same interpretation rules being followed to convert your text into a date as happens when you type a text string as a date into a cell. And those rules changed in version 3.6.2: some formats that worked previously no longer do so. This sort of thing is very locale-dependent, so I won't try to be definite about what is happening in your case. Your first question should be what happens if you enter 10/2013 into an unformatted cell: do you see a date value, perhaps 1 October 2013, or do you see the text string you entered?

Note that the date acceptance patterns can now be adjusted at Tools | Options... | Language Settings | Languages | Language of | Date acceptance patterns. Note also that the ISO format YYYY-MM-DD works in all locales, so is most reliable.

It occurs to me that you would never want to use a literal text value as in your example: 10 would be a lot easier to type than =MONTH("10/2013")! So you must be wanting to refer to the contents of a cell or some other expression, of course. In that case, you would presumably be best advised ether to store proper (numerical) dates in the first place, or else to construct them explicitly from the text values you have available instead of relying on the automatic interpretation.

Eike Rathke says more about this in his blog: http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html .

I trust this helps.

Brian Barker

#2

Brian Barker wrote:

=MONTH("10/2013") returns "10" in version 3.5.7, but returns "#VALUE!"
in version 4.x. The same applies to YEAR() as well. Is this a known
bug/feature or is there maybe something wrong in my settings?

I think this is a feature.

The definition of the MONTH() and YEAR() functions require their single
argument to be a date value - that is, a numerical value which can be
formatted as a date. I think in giving them a text argument, you are
relying on the same interpretation rules being followed to convert your
text into a date as happens when you type a text string as a date into a
cell. And those rules changed in version 3.6.2: some formats that
worked previously no longer do so. This sort of thing is very
locale-dependent, so I won't try to be definite about what is happening
in your case. Your first question should be what happens if you enter
10/2013 into an unformatted cell: do you see a date value, perhaps 1
October 2013, or do you see the text string you entered?

I see the text string entered, but the formatting says "Number - General".

Note that the date acceptance patterns can now be adjusted at Tools |
Options... | Language Settings | Languages | Language of | Date
acceptance patterns. Note also that the ISO format YYYY-MM-DD works in
all locales, so is most reliable.

When I add ;M/Y nothing changes. Or did you mean, that after this change
if I enter 10/2013 into the cell it should be formatted as date? Then
this doesn't work.

It occurs to me that you would never want to use a literal text value as
in your example: 10 would be a lot easier to type than
=MONTH("10/2013")! So you must be wanting to refer to the contents of a
cell or some other expression, of course. In that case, you would
presumably be best advised ether to store proper (numerical) dates in
the first place, or else to construct them explicitly from the text
values you have available instead of relying on the automatic
interpretation.

Of course my formula is referring to another cell :wink: I changed the
formatting of those cells to Date MM/YYYY and everything works now. I
"only" had to manually remove all those pesky ' from the beginning of
the expression, which appeared after I changed the formatting.

Thanks for your help

Thomas

#3

Hi Thomas,

Which version of 4.x ?

From 4.1 onwards, the date/time definitions were reworked. These changes

might be what is causing the difference in behaviour. In many instances,
date/time values are now structs as opposed to string representations,
and perhaps the Calc functions in question rely on that to work correctly.

Alex

#4

Alex Thurgood wrote:

Which version of 4.x ?

From 4.1 onwards, the date/time definitions were reworked. These changes

might be what is causing the difference in behaviour. In many instances,
date/time values are now structs as opposed to string representations,
and perhaps the Calc functions in question rely on that to work correctly.

I tried both, 4.0 and 4.1, hence the x. But yesterday I only tried 4.1.2.3

#5

Thomas Boehm wrote:

Brian Barker wrote:

in your case. Your first question should be what happens if you enter
10/2013 into an unformatted cell: do you see a date value, perhaps 1
October 2013, or do you see the text string you entered?

I see the text string entered, but the formatting says "Number - General".

Note that the date acceptance patterns can now be adjusted at Tools |
Options... | Language Settings | Languages | Language of | Date
acceptance patterns. Note also that the ISO format YYYY-MM-DD works in
all locales, so is most reliable.

When I add ;M/Y nothing changes. Or did you mean, that after this change
if I enter 10/2013 into the cell it should be formatted as date? Then
this doesn't work.

Of course I had to replace D/M with M/Y and not just add it. Now it
works and "01/10/13" appears when I enter "10/13".