Thanks Brian, This exactly how I solved it.
But cumbersome given that DATEVALUE seems to be intended to do just this. Thanks, Ferry Brian Barker schreef op ma 05-03-2012 om 00:32 [+0000]: > At 22:35 04/03/2012 +0100, Ferry Toth wrote: > >With the dutch localization DATEVALUE accepts 1-jan-2011 and > >1-mrt-2011 (correct dutch format) but not 1-mar-2011 (english US) > >regardless if I set the standard language for the document to > >English US or not. Only if I set the 'locale setting' to US does it > >accept 1-mar-2011. I am trying make sense of some weird data format > >(2011MAR) which is English based. To me that seems a common > >situation, and I would not want to change the LO global localization > >settings as that changes all date formats in the spreadsheet to > >english. Couldn't datavalue take the locale of the formatted cell? > >Or is there a better trick to ge this done? > > If you cannot find a direct way of doing this sort of thing, you can > always do the conversion yourself explicitly. It's no doubt sensible > for you to keep Dutch settings, so you will probably need to write up > the English month name abbreviations. Construct a table of these, > with JAN, FEB, MAR, and so on in one column and the numbers 1 to 12 > in the next column to the right. Select all twenty-four cells, go to > Insert | Names > | Define..., and give the table a name - perhaps "Months". > > Suppose your value "2011MAR" is in A1. Try this formula: > =DATE(LEFT(A1;4);VLOOKUP(RIGHT(A1;3);Months;2;0);1) > > The LEFT() function takes the first four characters as the year. The > RIGHT() function takes the last three characters, and the VLOOKUP() > function looks these up in the Months list to produce a month > number. These are combined by DATE() to give the first of the relevant month. > > I trust this helps. > > Brian Barker > > -- For unsubscribe instructions 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
