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

woomla · General User Joined: 12 Nov 2008 Posts: 11

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.

Villeroy · Posted: Mon Sep 05, 2011 2:31 am Post subject:

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 http://forum.openoffice.org

woomla · General User Joined: 12 Nov 2008 Posts: 11

Sorry for the late reply.

woomla · General User Joined: 12 Nov 2008 Posts: 11

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.

Villeroy · Posted: Mon Sep 26, 2011 7:33 am Post subject:

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 http://forum.openoffice.org

woomla · General User Joined: 12 Nov 2008 Posts: 11

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.

mgroenescheij · Posted: Mon Sep 26, 2011 11:54 pm Post subject:

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).

Villeroy · Posted: Tue Sep 27, 2011 12:29 am Post subject:

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 http://forum.openoffice.org

woomla · General User Joined: 12 Nov 2008 Posts: 11

@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.

mgroenescheij · Posted: Tue Sep 27, 2011 5:05 pm Post subject:

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).

Villeroy · Posted: Wed Sep 28, 2011 5:46 am Post subject:

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 http://forum.openoffice.org