Dot separated date in YYYY.MM.DD format

users
#1

Hello all,
When I enter 2015.01.01, it is recognized not as a date. How do I make it one step only? I enter it and it is detected as a date.

01-01-2015 is recognized as a date. Then I need to format this. Two steps are required. 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _

#2

I tried this, which seem to work: specifying the cell format with a custom
format YYYY.MM.DD. When I input a date in this form, it is kept this way,
and is recognized as a date (possible to use it in formula).

Is that not enough? Maybe I misunderstood the question.

#3

Add the input format to your Date acceptance pattern?

Tools -> Language Settings -> Languages: Language Of... Date acceptance
patterns

-=from en-US help=-

Date acceptance patterns

Specifies the date acceptance patterns for the current locale. Calc
spreadsheet and Writer table cell input needs to match locale dependent date
acceptance patterns before it is recognized as a valid date. Default locale
dependent date acceptance patterns are generated build time, but it is
possible to add more or modify them in this edit box.
Additionally to the date acceptance patterns defined here, every locale
accepts input in an ISO 8601 Y-M-D pattern, and since LibreOfficeDev 3.5
that also leads to the YYYY-MM-DD format being applied.
Syntax: Y means year, M means month, and D means day, regardless of
localizaton.

#4

Hi :slight_smile:
If the formatting is set to display the date in the way that you want then
when you type in;
19/12
then it should display as
19.12.2014
(or whatever your formatting is for dates).

So if you have a whole column, or row of dates then it's not really a 2
step process, more like (n+1)/n steps as the initial step set-up lots of
cells all at once.

Sorry that is not a huge help! It's an awkward work-around. You really
kinda need to be able to type in roughly the way it is meant to look.

Regards from
Tom :slight_smile:

#5

Aha! This is a different question: you need Calc to recognise your entered text as a date, to convert the text string to the internal number representing that date (that's probably 42005 in your example case), and to set the cell's format to a date format if you have not already done this.

There are two solutions to this:

o As has already been suggested, if this format is not accepted by default in your locale, you may be able to add it at Tools | Options... | Language Settings | Languages | Language of | Date acceptance patterns.

o Failing this, you could easily set up a formula. Enter your desired text into, say, column A. In B1, say, enter something like
=DATE(LEFT(A1;4);MID(A1;6;2);RIGHT(A1;2))
and fill this down the column. If you want to be able to read column B, you will need to format it with your desired format. But you may be happy reading column A instead. Any formulae for calculations depending on your input would depend on column B, so would work as soon as you had entered your text into A. So this technique passes your "one step" test.

o You might find that =DATEVALUE(A1) works as well, but I imagine that this will work only with date formats that follow one of the acceptance patterns anyway.

I trust this helps.

Brian Barker