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

Brian Barker wrote:
> 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?

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 ;-) 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

-- 
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