Date Format wrong

Discuss the spreadsheet application

Date Format wrong

Postby a1d3s » Tue Apr 15, 2008 8:04 pm

Hi there i open i new spreadsheet
Windows 2000 OOo 2.4.0 Final with JRE (all in German)

if i format a cell for Date like 12.03.2008 example
and type in this cell 120308 it format it to something like 04.24.23865

how can i change it
a1d3s
 
Posts: 2
Joined: Tue Apr 15, 2008 8:01 pm

Re: Date Format wrong

Postby Villeroy » Tue Apr 15, 2008 8:26 pm

Every single cell in a spreadsheet is can take any text or floating point number. Unlike databases there are no distinct field-types for dates, times or timestamps. Thus there are no input-forms with input masks. No spreadsheet can treat 120308 other than plain integer. When you format 120308 as date you get 2229-05-22 since this is the 120308th day since day zero, which is 1899-12-31 just like PI() formatted as date/time is 1900-01-02 03:23:54 [3.14159 days since day zero].
So your problem is related to wrong values rather than wrong date formats.

If you really want to type 6 digits for every date, enforce literal text input in one column (number format "Text") and derive a usable date value like this:
Code: Select all   Expand viewCollapse view
=IF(LEN(A1)=6;DATE(VALUE(LEFT(A1;2));VALUE(MID(A1;3;2));VALUE(RIGHT(A1;2)));"")
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: 28643
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date Format wrong

Postby kingfisher » Wed Apr 16, 2008 4:03 am

If your default date format is dd.mm.[yy]yy, you only need to type and enter 12.03 to get the date 12.03.08. If you had entered the date during March, 2008, you would only need to type and enter 12.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Date Format wrong

Postby a1d3s » Wed Apr 16, 2008 6:33 am

hm ok i will think about it ..

on M$ Execel the formated cell gives me 020308 -> 02.03.2008

but thanks :)
a1d3s
 
Posts: 2
Joined: Tue Apr 15, 2008 8:01 pm

Re: Date Format wrong

Postby Villeroy » Wed Apr 16, 2008 3:14 pm

a1d3s wrote:hm ok i will think about it ..

on M$ Execel the formated cell gives me 020308 -> 02.03.2008

but thanks :)

Really? Well, I'm not familiar with recent Excel versions anymore.
A1: 020308 -> 02.03.2008
=YEAR(A1) -> 2008 :?:
=MONTH(A1) -> 3 :?:
=DAY(A1) -> 2 :?:

 Edit: Now I recall this trick from older Excel versions (9 or 10?):
A1: 180408
B1 =VALUE(TEXT(A1;"00"".""00"".""00")) formatted as DDMMYYYY (Ger:TTMMJJJJ) -> 06042008
More elegant than my previous suggestion with DATE(VALUE(...);...) 
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: 28643
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests