Download it now!
Bug 92457 - Subformat ;@ changes date input to number
Summary: Subformat ;@ changes date input to number
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium minor
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-06-30 13:21 UTC by Buovjaga
Modified: 2017-01-08 21:00 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Buovjaga 2015-06-30 13:21:31 UTC
1. Right-click - Format cells
2. Enter into the Format code field: D\.M\.YY;@  (adjust according to your locale)
3. Notice how it is shown correctly in the preview (18.05.03)
4. Enter a date into the cell (f.ex. 30.6.2015)
5. Date displays as 29.1.00, input field shows 30.62015

The input field might show a whole or decimal number depending on the language you chose in the Format cells dialog.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 4fff776a08dd92121ab507a1ab506ac945abaedb
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-06-28_00:25:54
Locale: fi-FI (fi_FI)

Ubuntu 15.04 64-bit 
OpenOffice.org 3.3.0
OOO330m20 (Build:9567)
Comment 1 Buovjaga 2015-06-30 13:33:22 UTC
If I input a date in a new cell without any special formatting, copy the cell and then paste special - values only to the cell with the ;@ format, it shows correctly.
In my example 30.6.2015 the input value changes to 42185 on paste values only.
Comment 2 m.a.riosv 2015-06-30 23:22:13 UTC
Hi,
I'm not sure that the interpretation about how that format must work is right.

From inner help:
""""
Positive and Negative Numbers
To define a number format that adds a different text to a number depending on if the number is positive, negative, or equal to zero, use the following format:
"plus" 0;"minus" 0;"null" 0
""""
Text and Text
To include text in a number format that is applied to a cell that might contain text, enclose the text by double quotation marks (" "), and then add an at sign (@). For example, enter "Total for "@ to display "Total for December".
""""

For my understanding "/D\.M\.YY;@" define a format only for positive numbers and text:
- With a positive number or zero, use the date format.
- Negative it's not showed, has not a number format correspondence.
- Text it's showed, "@" is selected.
Comment 3 Eike Rathke 2015-07-09 20:35:29 UTC
Correct. Used with a date format that might stem from the fact that Excel can't handle dates with negative offset from the null date, i.e. it doesn't handle dates before 1900-01-01 at all.
Comment 4 Eike Rathke 2015-07-10 11:22:42 UTC
Actually Excel treats this special case different, a trailing ;@ text format acts as if it was entered as the 4th subformat and the 2nd and 3rd subformats inherit from the 1st, so  0;@  acts like  0;-0;0;@
In case of a date format of course not with a negative date..
I'll try to come up with a solution.
Comment 5 Commit Notification 2015-07-10 13:13:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f7f4d985ad44ba0c644b2be02d09002b13d749f7

Resolves: tdf#92457 handle trailing text subformat if others omitted

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Eike Rathke 2015-07-10 13:36:18 UTC
Pending review
https://gerrit.libreoffice.org/16925 for 5-0
https://gerrit.libreoffice.org/16927 for 4-4
Comment 7 Commit Notification 2015-07-10 14:27:50 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=89cfc660dd748daa3f6eee81665e4cbe502653d4

the main text format is not a trailing text format, tdf#92457 follow-up

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2015-07-10 14:45:35 UTC
Reopening, this needs further work on export filters.
Comment 9 Commit Notification 2015-07-11 01:09:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b3c11e2b3ef4bad8c2b5f92ba00ac805fb68786f

the last condition is "all other numbers", not the third, tdf#92457

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2015-07-11 01:09:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=934e35c62525a7541e6a5b2d05b557a6fcc35abb

store trailing text format in ODF, e.g. 0;@ tdf#92457

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2015-07-11 01:09:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dad6be8af0e670a56d3d399a1b0a35859bd7b093

write trailing text subformat also to Excel .xls and .xlsx, tdf#92457

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2015-07-13 11:32:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5af8cf2e94a07821e9058ed99997541b80dd6693

use the DBL_MAX constant that we also use elsewhere, tdf#92457

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2015-07-13 11:51:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=cdb2d6aa33a409994c3033cf621847d1552ce8cd&h=libreoffice-4-4

the last condition is "all other numbers", not the third, tdf#92457 related

It will be available in 4.4.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2015-07-13 11:51:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9d23401109f93fcff37f35f708af7ec33e690ed4&h=libreoffice-5-0

the last condition is "all other numbers", not the third, tdf#92457 related

It will be available in 5.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 15 Buovjaga 2015-07-14 11:06:49 UTC
It is now stored correctly in .ods, .xlsx and .xls.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+ (x64)
Build ID: 8de7d44f96252e97e224d42466698114370dbaa3
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-07-13_21:43:47
Locale: fi-FI (fi_FI)

Ubuntu 15.04 64-bit 
Version: 5.1.0.0.alpha1+
Build ID: 0bd582834b46dbbc5037310d45bac8885e6f2a07
TinderBox: Linux-rpm_deb-x86_64@46-TDF-dbg, Branch:master, Time: 2015-07-14_01:14:35
Locale: en-US (en_US.UTF-8)