How to format text values (dd.mm.yy) as date?

Discuss the spreadsheet application

How to format text values (dd.mm.yy) as date?

Postby robbbert » Sun Jun 22, 2008 8:40 am

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? :mrgreen:) or resetting my locale settings temporarily (how?).

Any ideas?
Thanks!
OOo 2.4.X on Ubuntu 8.x
robbbert
 
Posts: 1
Joined: Sun Jun 22, 2008 8:18 am

Re: How to format text values (dd.mm.yy) as date?

Postby squenson » Sun Jun 22, 2008 10:15 am

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.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: How to format text values (dd.mm.yy) as date?

Postby Villeroy » Sun Jun 22, 2008 10:20 am

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.

Code: Select all   Expand viewCollapse view
=VALUE(A1)

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:
Code: Select all   Expand viewCollapse view
=VALUE(A1)+STYLE("myDateStyle")


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")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 32 guests