Formatting a date

users
#1

I have text: 19 February 2015. The format for the cell is date 2/19/2015, but it will not acknowledge that or sort correctly.
As if it’s formatted for date but not accepting that fact.
What did I do wrong? [I am not currently subscribed to the list; sorry]

Mark W. Howe
San Juan Capistrano, CA
949-496-3453 home/ office
949-525-3914 cell [not for messages]

#2

o If you format a cell as Date and then type a date, the value will be edited from the text you type to an internal number - the number of days (and potentially fractions of a day) since the date origin. This will then be displayed as a date, in the format you chose.

o If you type the text "19 February 2015" into a cell you have not previously assigned a type, Calc will.(subject to the diktats of your locale) translate the date to the internal number and apply a default date format to the cell.

o But if you first enter text - even if it looks like a date to you and me - and then apply a date format, you are asking Calc to format text as a date. But it cannot do that - at least, it cannot do so without amending the actual contents of the cell, which would be undesirable.

If you want your existing values to be genuine dates - as you will want to do if you need to include them in calculations or even just sort them - you must first convert the text into the equivalent date. Fortunately. the DATEVALUE() function is available to do this for you. Just put =DATEVALUE(Xn) in another column and fill down. You can copy the results back over your original data if you prefer, but you will have to use Paste Special and ensure that Formulae is *not* ticked in the Paste Special dialogue. Once you format the cell as your preferred Date format, you will have what you need.

I trust this helps.

Brian Barker

#3

I interpreted what you wrote to mean that =datevalue(a1) would convert the contents of A1 into a date. It did not and produced err502. What am I missing?

Thank you for helping so quickly :slight_smile:

Mark W. Howe
San Juan Capistrano, CA
949-496-3453 home/ office
949-525-3914 cell [not for messages]

"Phao Binh" http://swcenter.fortlewis.edu/finding_aids/inventory/2008008.pdf

#4

Sent: Thursday, February 19, 2015 7:05 AM
Subject: Re: [libreoffice-users] Formatting a date

I have text: 19 February 2015. The format for the cell is date 2/19/2015, but it will not acknowledge that or sort correctly. As if it's formatted for date but not accepting that fact. What did I do wrong?

If you want your existing values to be genuine dates - as you will want to do if you need to include them in calculations or even just sort them - you must first convert the text into the equivalent date. Fortunately. the DATEVALUE() function is available to do this for you. Just put =DATEVALUE(Xn) in another column and fill down.

I interpreted what you wrote to mean that =datevalue(a1) would convert the contents of A1 into a date.

Yup - provided that the value in A1 is a "valid date expression".

It did not and produced err502. What am I missing?

Then what is in A1 cannot be what Calc considers (in your locale) to be a valid date expression. The DATEVALUE() function seems very robust to me: it can cope with many date formats and doesn't seem to be fazed by extra blanks, though it can be by extraneous commas.

Is it possible that you have a column heading in A1?

I trust this helps.

Brian Barker

#5

Further to the formatting of cells/columns to hold dates (Format -> Cells:
Category Dates)
which then have a configurable display format.

The date pattern acceptance formats can be extended (Tools -> Options ->
Language Settings -> Languages: Language Of "Date acceptance patterns")

Beyond defaults, append the text format of any textual content that you need
to convert into dates.

=-ref-=
https://help.libreoffice.org/Common/Languages#Date_acceptance_patterns

#6

Yeah, I tho't of that and tried changing it around to 14 Jan 2014.
The only thing that worked was changing it to Jan 14 2014 and then it worked automatically in it’s own column making the exercise with datevalue of no value.

      14 Jan 2014 Err:502
      01/10/14 Err:502
      01/02/14 Err:502
      25 February 2014 Err:502
      25 February 2014 Err:502
      25 February 2014 '=DATEVALUE(A6)
      30 March 2014
     
This is what it looks like, 2 col starting with A1 [no col heading]. Line 6 so’s you can see what the formula looks like.

Mark W. Howe
San Juan Capistrano, CA
949-496-3453 home/ office
949-525-3914 cell [not for messages]

"Phao Binh" http://swcenter.fortlewis.edu/finding_aids/inventory/2008008.pdf

#7

Hi :slight_smile:
So it's picking up the USA locale for it's input? I thought LibreOffice
could have it's own locale as somethign different from the system's one?
Regards from
Tom :slight_smile:

#8

Hi.
What version of LO do you have . I have 4.2 and 4.3.
If I type 15 February 2015 into a cell it converts to a date (15/02/15 for me).
If I format a cell (say A2) as text and type 15 February 2015 into a cell it shows 15 February 2015.
If I enter =DATEVALUE(A2) into cell B2 then I get a number. I can then format that number (Format>Cell) as a date value.

Are the dates you entered typed in, could there be any extraneous characters.
steve

#9

If you right click Format>Cell>Numbers what comes up should be the current formatting for the cell.
DATEVALUE() seems to work only on text. I can add spaces and remove spaces without a problem but not add extraneous characters except using a - as a single separator.
Steve

#10

I was able to fool it by using what you all taught me with ‘concatenate’. I split it into 3 cols with the space, then switch the cols and concatenate back to MM DD YYYY. However I do not understand why an international program like LiebreOffice cannot recognize DD MM YYYY.

Mark W. Howe
San Juan Capistrano, CA
949-496-3453 home/ office
949-525-3914 cell [not for messages]

"Phao Binh" http://swcenter.fortlewis.edu/finding_aids/inventory/2008008.pdf

#11

1. Do not type dates into cells that are formatted as text and do not type
dates with a leading apostrophe
2. Do not import dates as text values from csv or html files.
3. When you paste content from another program, then do not hit Ctrl+V. Use
the paste-special command, import raw text data and import the date column
as dates

With the right method of input/import/paste your dates will sort properly
and you can calculate all kinds of time intervals, time differences,
aggregate by months, years, weeks etc.

Import options for numeric text data when using paste-special or when
opening text tables (csv):
1) The "special numbers" option should _always_ be checked. Special numbers
are dates, times, booleans, numbers with currency symbols, any kind of
number with more than minus sign, digits and a decimal separator. ALWAYS
check this option.
2) "quoted numbers as text": Some text files there are quotes around numbers
(e.g. zip or phone numbers) in order to mark them as text. WIth this option
checked, quoted values are always imported as text.
Now for the most important option in the whole dialog:
3) Language. Choose English(USA) if 1/2/2015 should be interpreted as 2nd of
January and decimals have a point (3.14)
Choose English(UK) if 1/2/2015 should be interpreted as 1st of February.
1.2.2015 looks like a typical German date together with decimals like this:
3,14 (comma decimal).
If the file has currency values like ￥3.14 you would choose Japanese.
If the file has currency values like 3,14 RUR you would choose Russian
import language.
4) the preview table on the bottom of the import dialog lets you select
columns and mark them as DMY dates, MDY dates,YMD dates, US English or Text.
This overrides all the other options for the selected columns.
When you have special numbers checked and disregard any quotes around
numbers and now there is a field that looks like "3-4" (meaning "3 to 4" but
software can not know what it means) then you should mark this column as
text. Otherwise it may be interpreted as special number 4th of March with US
language or 3rd of April with any other language.

If you have entered/imported/pasted wrong data (numeric text):
Formatting will not (and must not) change any data. You need to re-enter all
data. But you don't need to do that manually.
Semi-automatical method to re-enter all data of the selected cells:
1)Select the column(s) in question.
2)Apply the right number format language and any number format you like to
see afterwards.
3) Edit>find&replace...
   [More Options]
  [X] Current selection
  [X] Regular expressions
Search: .+ (a dot and a plus)
Replace: & (ampersant)
[Replace All]

Caveat: If you imported dates with the wrong language option, then you may
have a mix of wrong dates and text values:
01/02/14 (meant to be 1st of February but the actual cell value is 2nd of
January )
13/02/14 (meant to be 13th of February but the actual cell value is text
because in US context there is no 13th month )

HighlightValues[Ctrl+F8] shows the text dates in black font and the

wrong dates in blue font.
In this difficult case you need a formula to interchange months and days for
the wrong dates and then convert the text cells into dates.
=DATE(YEAR(A1);DAY(A1);MONTH(A1)) turns 1st of February into 2nd of Januaray
and vice versa.

#12

P.S. to the special case where you imported "normal" dates in US context
ending up with a column of wrong dates and text.

Assuming that you don't have the original data at hand so you can not repeat
the data import with appropriate import options, this is how to correct
wrong values:
1) Switch the locale option under Tools>Options>LanguageSettings>Languages
from English(USA) to English(UK).
Now the wrong date 1/2/14 switches to 2/1/14 in UK context but it is still
the same wrong date (2nd of February).
The text values like 13/1/2014 remain the same text values but now they show
a leading apostrophe in order to mark them as literal text and prevent
numeric evaluation. In UK context 13/2/14 would be a correct date but the
actual cell values remain stable when playing with language options and
formattings. This is an important feature. It is not a bug by any means.

2) Apply the following formula to the entire date column (assuming dates
starting in A1):
=IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1));VALUE(A1))
The formula returns the corrected date if A1 has a wrong date and converts
the text in A1 to a number if the A1 has a text value. The conversion from
text to number happens in the globally set UK context set in step 1). Don't
panic if the converted text values are shown as integer numbers. These
integer numbers are the correct values.
Copy the formula column, select the source column (A in this example) and
paste-special values only (no formulas).
Reset the locale back to US. Now the correct dates switch from 13/1/2014 to
1/13/2014 but they are still the same correct dates.
Format the cells to your liking. Notice that the number format dialog lets
you override the locale option for the selected cell. This has no influence
on the actual cell values which are the right ones after this conversion.