OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to edit a date cell as I first entered it.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
woomla
General User
General User


Joined: 12 Nov 2008
Posts: 11

PostPosted: Mon Sep 05, 2011 2:13 am    Post subject: How to edit a date cell as I first entered it. Reply with quote

Hi,

I can type in a datetime value into a cell, and set the display exactly as I type it. It displays correctly. But when I edit the cell, it turns into another format. Particulary, it is missing milliseconds,and the date is in another format to.

Example:
I enter: 2011-09-05 11:11:57.842
Display: 2011-09-05 11:11:57.842
Input line: 09/05/2011 11:11:58
edit: 09/05/2011 11:11:58.

This is horrible, even the second is changed. Changing the value requires to remember the value and enter it again (at least the second and milliseconds).

I cannot find any setting for it. How do I get my entered text back?

W.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 05, 2011 2:31 am    Post subject: Reply with quote

Calc does not auto-format your values.

Format the cells as you like (English ISO dates in this case)
That is YYYY-MM-DD HH:MM:SS.000 with English number format locale.
Then enter your dates anyway you like:
4/ => this month's 4th day
4/8 => this year's 4th of August or 8th of April (US locale)
There are many other ways to enter a date, depending on the locale setting.

The value will always be displayed as formatted, not as entered.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
woomla
General User
General User


Joined: 12 Nov 2008
Posts: 11

PostPosted: Mon Sep 26, 2011 7:01 am    Post subject: Reply with quote

Sorry for the late reply.

Quote:
The value will always be displayed as formatted, not as entered.


This is not true in my case. (OOo 3.3.0 b9567)

I format the cell as yyyy-mm-dd hh:mm:ss.000.
I input 2011-04-08 12:34:56.789
The cell displays 2011-04-08 12:34:56.789.
The input line displays: 04/08/2011 12:34:57.
If I edit the contents of the cell I get the same 04/08/2011 12:34:57.

Calc just eats my milliseconds away Sad
Back to top
View user's profile Send private message
woomla
General User
General User


Joined: 12 Nov 2008
Posts: 11

PostPosted: Mon Sep 26, 2011 7:14 am    Post subject: Reply with quote

Here are some examples what I see (notice the seconds that are changed in edit)

Entering datetime:



After entering the contents in the cell are looking good. But the input line is not what I've entered.



When I edit the cell, it is as bad as the input line.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Sep 26, 2011 7:33 am    Post subject: Reply with quote

The input line is exactly the same value you have entered down to 1/100 of a second.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
woomla
General User
General User


Joined: 12 Nov 2008
Posts: 11

PostPosted: Mon Sep 26, 2011 11:22 pm    Post subject: Reply with quote

I probably do not understand you correctly.

The input line in the first image ("Entering datetime") is what I enter:
2011-04-08 12:34:56.789

The input line in the second image ("After entering the contents...") is what is left after I've entered the value:
04/08/2011 12:34:57

This value in the input line is not what I've entered nor how I formatted the text.
The value in the cell is how I formatted the text.
The value in the input line truncates the milliseconds. It is rounded to the nearest second.
When I edit the cell, the value is also rounded to the nearest second.

I want to be able to edit the milliseconds. Now I have to retype them. Off course that will work, as well as entering a complete new value in the cell. But I want to change the content in the cell, also the milliseconds. I do not want it to be rounded to the nearest second.
Back to top
View user's profile Send private message
mgroenescheij
Super User
Super User


Joined: 20 Apr 2011
Posts: 870
Location: Australia

PostPosted: Mon Sep 26, 2011 11:54 pm    Post subject: Reply with quote

Hi,

Go to the Format Menu and select Cells in the First tab (Numbers) select Date and in the Format code field enter: YYYY-MM-DD HH:MM:SS.

How difficult could that be?

Martin
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Sep 27, 2011 12:29 am    Post subject: Reply with quote

I see. The formula bar should nut cut off the fractions of seconds when you hit the enter key. If you need to see the fractions there is no way other than showing them in the cell. The missing decimals are still there even when you don't see them.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
woomla
General User
General User


Joined: 12 Nov 2008
Posts: 11

PostPosted: Tue Sep 27, 2011 2:43 am    Post subject: Reply with quote

@Villeroy: I've filled a bug for it because I believe it is one. https://issues.apache.org/ooo/show_bug.cgi?id=118467


@mgroenescheij: The format code I used is "yyyy-mm-dd hh:mm:ss.000" This formats the cell the way you can see in the second image. That's how I want it to be formatted. And the cell displays it's value correctly.

It is the editing that is not correct. The formula bar and the edit window cut off the fractions of the seconds as you can see in the third image. Calc should not do that.
Back to top
View user's profile Send private message
mgroenescheij
Super User
Super User


Joined: 20 Apr 2011
Posts: 870
Location: Australia

PostPosted: Tue Sep 27, 2011 5:05 pm    Post subject: Reply with quote

Hi,

OpenOffice allows you to set the format of a cell not the format of the data entry field.
As date time values are stored as numeric values OpenOffice has now knowledge of how you have entered the data.
I agree that it should at least display the milliseconds.

Martin
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Sep 28, 2011 5:46 am    Post subject: Reply with quote

It would be an important enhancement if the formula bar could show the exact value with a max. of 15 significant digits for all numbers and 1/100th of a second for dates and times.

Currently, you see the whole cell value regardless of cell formatting unless it is a date, time, date+time or boolean.
All other number format categories show 3.14159265358979 for the number pi.
Boolean should display TRUE in the cell and 3.14159265358979 in the formula bar.
Any date/time format should yield the full value like this: 1900-01-02 03:23:53.61 (full iso format with all possible digits)
Personally I would prefer 3.14159265358979 in the formula bar even for date/times.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group