[Solved] Cell format not changing

[phjvdmeij]

I can not change the format of some cells of a column from number to percentage.( 6 out of 100)
All the cells contain the same formula
They also contain the same conditional colour formatting

if I clear the conditional formatting for the cell then the problem disappears
If I copy/paste the format from an other cell back to the cell then the problem re-occurs

[Villeroy]

Conditional_Formatting.ods

(14.84 KiB) Downloaded 237 times

[phjvdmeij]

The format cell problem is for example present in the attached file sheet 5 column G1

if I clear the date in column J1 the problem disappears
if I change the date in J1 from 2017 to 2016 the problem disappears

[robleyd]

J1:J3 are formatted as text, not date. Try correcting that and see what happens.

[Villeroy]

Apply a date format and re-enter the dates.

How to re-enter many dates:
Edit>Find&Replace...
[More Options]
[X] Current selection
[X] Regular Expressions
Search: .+ [dot plus]
Replace: &
[Replace All]

I can not change the format of some cells of a column from number to percentage.( 6 out of 100)

A cell value IS either one of text or number. If it IS a text, no formatting attribute can change the type of value.

[phjvdmeij]

the format of text for the date value is not the issue. It was set in the experiments, but back to date format makes no difference.

the difference in behaviour between row 1 and row 2,3 is the strange thing.
also the fact that a different date in J1 changes the format of G1 looks like that there goes something wrong in the calculation and presentation of the complete row.

[Villeroy]

Cells N2 and O3 contain text (white space).

[phjvdmeij]

I 'solved' the problem as follows:

copy row 1
delete row 1
insert row 1
paste data back in row 1
re-instate the conditional format of j1 (copy j2, paste special (format))

[MrProgrammer]

Hi, and welcome to the forum.

I can not change the format of some cells of a column from number to percentage.( 6 out of 100)

The format cell problem is for example present in the attached file sheet 5 column G1

You have conditional formatting applied to G1 which controls the display format. G1 with value 19E-2 is assigned cell style gt. Cell style gt is assigned number format Standaard which does not display values as a percentage. You cannot override this by manually changing the number format with the Format → Cells dialog.

You have five choices to display G1 with a percentage format:
• Remove the conditional formatting and set the format with Format ← Cells
• Remove the conditional formatting and set the format with Format → Styles and Formatting (better)
• Adjust the cell style gt assigned by conditional formatting so it uses a percentage number format
• Adjust the conditional formatting selections to trigger a different style for the value in G1
• Change the value in the cell so it does not trigger any of the conditional formats

Value -372E-004 in cell G2 is an example of the final choice. It is neither less than -5E-2 nor greater than 5E-2.

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[phjvdmeij]

Hi,

Thanks for this excellent explanation:
I can live perfectly well without the conditional formatting for these cells.

I only remark that the implementation of the conditional formatting feature is not fully consistent, since it worked for some cells, and not for other cells that belonged to the
same column-G and contained the some formula. In the different data for the formula in those cells i saw no reason for this behaviour.
Also the effect of changing a date in a different cell-J of the same row was strange ( I changed the date and the cell format in cell-G changed from numeric to percentage)


I realise that without the complete sheet this is impossible to verify, but I think I have had enough support

thanks