Apache OpenOffice Community Forum

Hi,

There is an existing spreadsheet containing date values in the format dd.mm.yy. The cells are formatted as Text.

My system (Ubuntu Linux) language is English, and in a newly created spreadsheet I can enter dates in formats like mm/dd/yy or yyy-mm-dd (but not in the format dd.mm.yy (as that's not listed in the date format listbox of the cells formatter dialog)).

Now when I try to reformat the cells mentioned at first to become "dates", their format does but their values does not (actually). Instead Calc adds a leading apostrophe (') to their values.

Finally I would like to sort the spreadsheet by the values of those cells, thus I'd like to have them in either one of these formats: a) yyyy-mm-dd (Text) or b) anything (Date). - How to accomplish that.

I could think of writing some ugly macro (does some beautiful macro already exist? ) or resetting my locale settings temporarily (how?).

Any ideas?
Thanks!

You can perform a powerful "find and replace" using regular expressions:
1. Click on Edit > Find and Replace
2. Type exactly ([0-9]+)\.([0-9]+)\.([0-9]+) in the "Search For" box. No space in this string!
3. Type exactly $2-$1-$3 in the "Replace with" box. Again, no space!
4. Click on the button "More options" and select the "Regular Expressions" checkbox.
5. Click on "Replace All"

The first expression looks for a pattern of 1 or more digits, followed by a dot, followed by one or more digit, a dot and finally one or more digit. This is the pattern of your string. The parenthesis defines the three parts of the string (dd, mm and yyyy). Once found, you want to reorder this by starting with the month (2nd value represented by $2), a dash, the day ($1), a dash and finally the year ($3).

If you want, you can record this as a macro for future use.

Has nothing to do with formatting in the first place. First convert the value from text to number (if possible). Number formats are pointless if the value to be formatted is no number at all.
then apply any format you like, preferably through a cell style.

Having your preferred date-style, let the formula do the formatting on the fly: The conversion should work with ISO "YYYY-MM-DD" in all languages, otherwise the conversion depends on the locale as set in Tools>Options...Language Settings>Languages:Locale setting. Locale "Default" defaults to the same language as operating system and may look different on other systems without affecting actual values (US "12/31/1999" and British "31/12/1999"). Your style can use any number format locale explicitly to "fixate" the number format.
Edit: Squenson's method modifies the text values. ISO format sorts well as number and text as long as all text includes 4-digit years, 2-digit monts and 2-digit days ("1999-1-1" vs. "1999-01-01", vs "1999-11-11")