Bug 108373 - Conditional formatting changes format of dates and numbers
Summary: Conditional formatting changes format of dates and numbers
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting Number-Format
  Show dependency treegraph
Reported: 2017-06-06 19:48 UTC by john4070
Modified: 2022-06-17 03:32 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Sample ledgers. Formatted area on left includes check Number, sample on right does not. (32.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-06 19:54 UTC, john4070
changing column B to C (31.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-07 15:13 UTC, Xavier Van Wijmeersch
decimal and currency with conditional formatting (31.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-07 17:56 UTC, Xavier Van Wijmeersch
working conditional formatting (31.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-09 13:07 UTC, Xavier Van Wijmeersch

Note You need to log in before you can comment on or make changes to this bug.
Description john4070 2017-06-06 19:48:06 UTC
I have a sample ledger with columns B through H as Check No., Date, FirstName, LastName, Income, Expense, and Balance.  I want odd months to be shaded (light blue).  Date format:  6/6/17; numbers are all with 2 decimal places.
Conditional format using "=ISODD(MONTH($C6))" where C6 is the top date, makes all odd month rows light blue BUT CHANGES THE FORMAT OF DATES to their date number AND changes most whole numbers deleting their decimals (67.00 becomes 67, etc.)
I can change the number days between dates at will to help demonstrate the problem.
This does not happen in Excel - I had to check!

Steps to Reproduce:
1.Removing the conditional formatting OR changing the difference in dates to make more or fewer bands makes those dates changed to colorless (even months) back to their normal date format, 06/06/17, etc. AND number regain their decimal places!

Actual Results:  
I'll be happy to email my sample sheet if you'll let me know how and where.  

Expected Results:
See above.  Dates should not change their format.  Numbers also.

Reproducible: Always

User Profile Reset: No - - - and your instructions, below, do not work!  

Additional Info:
I can't believe others haven't run into this problem!

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36
Comment 1 john4070 2017-06-06 19:54:27 UTC
Created attachment 133879 [details]
Sample ledgers. Formatted area on left includes check Number, sample on right does not.

Change date differences in yellow cell L30 and see the date formats go back and forth.

(I'm sure I'm doing something wrong but what is it?  This does not happen in Excel - and I'm not using Excel for this app!)
Comment 2 m_a_riosv 2017-06-06 23:57:13 UTC
Build ID: 6b2b689e944be554f7882b349332686755ce6c4b
CPU threads: 4; OS: Windows 6.19; UI render: GL; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-06-01_06:25:09
Locale: es-ES (es_ES); Calc: CL
Comment 3 Xavier Van Wijmeersch 2017-06-07 15:13:42 UTC
Created attachment 133899 [details]
changing column B to C

I confirm the behavior with conditional formatting3 
but changing column B to C then there is a normal result see attachment

Build ID: ddf8539d97ce044b7df8d51d6ec72ec864b40fb8
CPU threads: 2; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:master, Time: 2017-06-04_22:00:43
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 4 Xavier Van Wijmeersch 2017-06-07 17:56:20 UTC
Created attachment 133903 [details]
decimal and currency with conditional formatting
Comment 5 john4070 2017-06-08 18:43:54 UTC
I knew that changing the date and check number columns would work but I use the check number in lookups elsewhere and need to preseerve its location as the leftmost column.
I see that changing to pounds from dollars makes a difference but my funds are all in dollars.
As indicated, this works fine in Excel.  No problem.  I do want to do this in LO however!
Comment 6 Xavier Van Wijmeersch 2017-06-09 13:07:33 UTC
Created attachment 133926 [details]
working conditional formatting

you did not tel the check number was with a vlookup
so i used your first example and made it work with us dollar
its not a bug, there some forums where you can ask help like
ask.libreoffice or forum openoffice
these volunteers can help you for a solution like yours
Comment 7 Eike Rathke 2017-06-15 17:37:19 UTC
Nevertheless it *is* a bug. As soon as the content of a cell in column B is deleted, the neighbouring cell in the same row in column C gets displayed correctly.

@moggi: you might be interested in this one..
Comment 8 QA Administrators 2018-06-16 02:46:18 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2020-06-16 03:42:27 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2022-06-17 03:32:45 UTC
Dear john4070,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team