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: [email protected]
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
