[Solved] Date formatting problem in Calc

Discuss the spreadsheet application

[Solved] Date formatting problem in Calc

Postby Baasha » Fri Feb 07, 2020 3:14 am

I am using the latest version of Libreoffice Calc 6.4.
I have a column in a spreadsheet formatted as a date with user-defined format of DD/MM/YY.
When I enter 31/12/99 and then press enter the cell shows 31/12/99 as expected. However, if I enter the same thing and then press an arrow key to go to an adjacent cell I get 30/01/00. I can go back and re-enter the date and then press the arrow key and I get 31/12/99.

I have tried adding a date acceptance code of DD/MM/YY in the options but that makes no difference. Does anyone know how to correct this?
Last edited by Baasha on Fri Feb 07, 2020 9:24 pm, edited 1 time in total.
Baasha
 
Posts: 56
Joined: Fri Apr 11, 2008 9:32 pm

Re: Date formatting problem in Calc

Postby RusselB » Fri Feb 07, 2020 4:12 am

What is your locale? When I tried using the instructions you gave, I got the change in date no matter what I did.
My locale is Canada
Technically the entry of 31/12/99 entered on Feb. 6, 2020 turned into a date of Jan 30, 1900
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6097
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Date formatting problem in Calc

Postby Lupp » Fri Feb 07, 2020 11:51 am

First of all: Don't use the outdated "slash-two-digit-year" date formats. Yo cannot even tell from that what year Calc actually "recongnized". Exclusively use YYYY-MM-DD in spreadsheets. This is also the one unambiguous and internationally standardized format for exchanging dates in texts (what you have, e.g, if you type something into the editor of this forum).

The issues related to bad date formats in this forum are legion.

BTW: Date acceptance patterns always give only one letter per calendaric part of a date. Cocerning the intention from the OQ The pattern was D/M/Y, and this pattern is set as one of the defaults of non-US en locales afaik.

Also @Baasha: Create a signature for your account here, telling that you are using LibreOffice (and what version). AOO has no "Date acceptance pattern" setting.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2910
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Date formatting problem in Calc

Postby Villeroy » Fri Feb 07, 2020 12:59 pm

Try these date acceptance patterns:
D/;D/M;D/M/Y
with US English locale it should be:
D/;M/D;M/D/Y

this will behave like Excel or OpenOffice.
23/ enters this month's 23rd day
23/4 or 4/23 (US) enters this year's 23rd April
The dates can be entered on the num pad of a regular PC keyboard.

ISO dates YYYY-MM-DD are recognized with any locale and with any acceptance pattern. The draw back with ISO format is that you have to type more digits when entering recent dates. The good thing is that they are unambiguous and convertable from text values. ="2020-02-07"+7 returns the correct value for 14th Feb 2020 from a text and a number.
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: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date formatting problem in Calc (SOLVED)

Postby Baasha » Fri Feb 07, 2020 9:26 pm

Thanks Villeroy. The date acceptance patterns you suggested solved the problem.
Baasha
 
Posts: 56
Joined: Fri Apr 11, 2008 9:32 pm

Re: Date formatting problem in Calc (SOLVED)

Postby Lupp » Fri Feb 07, 2020 9:46 pm

Concerning dates the acceptance patterns and the display format are independent.

Personally I use (as shortcut) one of the acceptance patterns
Code: Select all   Expand viewCollapse view
D.M..;D/M//
(regard the two points or two slashes at the end for disambiguation from structured numbering) for input, but for output exclusively
Code: Select all   Expand viewCollapse view
YYYY-MM-DD
The input 3.5. thus ends up as the text "3.5." while 3.5.. (during year 2020) is converted to the numeric representation of 2020-05-03. This works fine for me. My UI and locale: English (UK). If I often had to enter dates from different years I would complete my acceptance patterns to
Code: Select all   Expand viewCollapse view
D.M..;D/M//;D.M.Y.;D/M/Y/
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2910
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests