Re: [libreoffice-users] MONTH() and YEAR() functions behave differently in scalc 3.5.7 and 4.x

At 21:59 06/10/2013 +0200, Thomas Boehm 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?

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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted