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)
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.
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.
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.
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.
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.
Pending review https://gerrit.libreoffice.org/16925 for 5-0 https://gerrit.libreoffice.org/16927 for 4-4
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.
Reopening, this needs further work on export filters.
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.
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.
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.
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.
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.
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.
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)