• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Switch between number formats in Conditionnal Formating

C

cyrilz

New Member
Hello all,


I want to conditional format a range of cell in "Short Date" or "Standard" if the condition is "Day" or "Week".


When I select "Day" the format is OK, but when I switch to Week the format stays in "Short Date". I need to press F2 then enter to see the number format.


Any clue ?


Regards
 
Works Ok in Excel 2010


Have you set 2 Conditions (1 for Day and 1 for Week) and not just assumed one is the default?
 
Yes,


I've put two conditions, and checked the stop if valid checkbox.

I've forgot to mention this worked fine in 2003 (Fernando's dashboard), but not in 2007.
 
Cyrilz

I also have it working ok in 2007

I have entered 2 lines in the Conditional Formating

=$H$1="Day"

=$H$1="Wk"

and set appropriate formats for each

"dddd dd mmm yy"

"dd mmm yy" etc

I haven't checked "Stop if Valid" box


Do you have calculations turned to Manual


If that doesn't work Clear Contents of the cells with dates and set Display Format to General, then Redo Conditinal Formating
 
Hello Hui,


I've based the formating against the value of the cell (< 30000 or > 30000)

but now I've changed to your way (which seems better with less calculations).

Format 1 is "dd/mm", format 2 is "Standard."


Now the behaviour is a bit different :


When I select "day" I have the date format (which is ok)

When I select "Week" I have the date format. I press F2 then enter it switches

When I select back "Day", the format is "date"... (repeat then).


I've also checked that the format in the cells is Standard by default.


ANy (other) clue :) ?
 
Cyril

Still works ok for me, the way I have interpreted how you have described it, and still No need for F2


Can you type out your rules exactly as you have then in Conditional Formatting or

post a small example of what your problem is somewhere
 
Hello Hui,


Here's my scrennshots (in French, I can't translate screenshots in English :)


Starting point : Planned / Days

http://dl.dropbox.com/u/4900864/Gantt000.jpg


Switch to Weeks ==> Wrong format (as shown in img 002)

http://dl.dropbox.com/u/4900864/Gantt001.jpg

http://dl.dropbox.com/u/4900864/Gantt002.jpg


Conditional format rules :

http://dl.dropbox.com/u/4900864/Gantt003.jpg


Press F2 in cell and ENTER :

http://dl.dropbox.com/u/4900864/Gantt004.jpg

http://dl.dropbox.com/u/4900864/Gantt005.jpg


Thanks in advance if you see any mistake :)


Cyril.
 
Hello Hui,


Here's my screenshots (in French, I can't translate screenshots in English :)


Starting point : Planned / Days

Code: 
http://dl.dropbox.com/u/4900864/Gantt000.jpg


Switch to Weeks ==> Wrong format (as shown in img 002)

[code]http://dl.dropbox.com/u/4900864/Gantt001.jpg

[code]http://dl.dropbox.com/u/4900864/Gantt002.jpg


Conditional format rules :

http://dl.dropbox.com/u/4900864/Gantt003.jpg

N5 contains "Week" or "Day", and is compared to reference cells.


Press F2 in cell and ENTER :

http://dl.dropbox.com/u/4900864/Gantt004.jpg[/code]

http://dl.dropbox.com/u/4900864/Gantt005.jpg[/code]


Thanks in advance if you see any mistake :)


Cyril.
 
Cyril

Not sure exactly what is wrong as I can't read french or see the entire formulas

but it looks like there may be a clash in your logic.

CF: Line 1 is Value = si(N5)

CF: Line 2 is Formula N5=P1 and it will stop if true and not process rule 3

CF: Line 3 is Formula N5=Q1, will not be processed if line 2 is True and should be the same as line 2, ie P1 or vise versa


The following works for me

http://i48.tinypic.com/2sb01gw.png
 
Hello Hui, and thank you.


P1 contains "Day" and Q1 contains "week" since they are used for data validation (list) in N5.


I've changed the 3rd line to format Number (0 decimal). Nothing changes. But I've noticed that wherever I refresh a cell correct formating appears.


Could this be because I've got plenty formulaes in my sheet and XL has difficulties refreshing ?
 
Cyril

The size of the spreadsheet shouldn't matter


Why do you need the 3rd rule?

Is Calculation set to Manual or Automatic?

Can you post the spreadsheet somewhere?


This works for me as per your details

http://i45.tinypic.com/2n84rie.png
 
Here it is :


Code: 
http://dl.dropbox.com/u/4900864/ganttSample.xlsm


Note : I removed all sensible data ;-)
 
Cyril


Firstly, That is a great looking dashboard, Well Done.


Secondly, That is a bizarre issue that is happening with Conditional Formatting

It looks like it is struggling to Change the Number format from Date to Number or back again. Which is the whole idea of Conditional Formatting ?


But as always there is a workaround ! ! !


Delete all the Conditional formating in the Range K6:AJ6


Copy and paste the following code in the code page for the "Tableau de Bord" worksheet in the VBA dialog

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Worksheets("Tableau de Bord").Range("N5").Value = "jour" Then
'
Range("K6:AJ6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "dd/mm"

Else
'
Worksheets("Tableau de Bord").Range("K6:AJ6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "#,##0"

End If

For Each c In Worksheets("Tableau de Bord").Range("K6:AJ6")
If c.Value = Worksheets("Tableau de Bord").Range("X3").Value Then
With c.Interior
.Pattern = xlSolid
.PatternColor = 0
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End If
Next

End Sub
[/pre]
 
Hello Hui,

thanks for your kind comments, it's always a pleasure to hear this from you (and Chandoo of course :)


Nice idea, but I've avoided macros from now on (apart the DonneSemaine function).


But I'll give this a try, adding the following test :

[pre]
Code: 
If Target.Address() = "$N$5" Then
...
End If
[/pre]

in order to avoid unnecessary computations when any cell changes.


Cyril
 
Cyril


I'm going to try this out tonight with both XL2003 and 2010 and let you know what happens, but I suspect it is a bug.
 
Hui,


Did you mean the behaviour of my worksheet ?


If yes --> I use XL2007 iferror() function regularly in my sheets, so I don't expect it to work in 2003.


If not --> I add the IF test because I suspect the function xill be called each time a cell changes.
 
No I will test the Conditional Format problem in both versions


Update: The Conditional formatting problem has been solved in 2010, so there must have been a problem with Conditional Formatting in 2007.
 
@Hui & @Cyril... I have noticed this peculiarity earlier too.


I debugged it for a while and then eventually gave up and used TEXT() formula to resolve it.


See this example:


=if(a1="short",text(datevalue,"dd-mmm"),text(datevalue,"dddd dd mmm yy"))


ofcourse, this would make the values in cells as text (so if you have any connected formulas that expect dates in the cells, you should use source dates as refs for them).
 
@Chandoo

The cells are used extensively elsewhere. It could be worked around, but would add to the complexity of the dashboard.


It is a very neat Dashboard which Cyril should be proud of.


Update 2: The Conditional formatting problem wasn't there in 2003 as 2003 never had the facility to vary the number formats in Conditional Formatting.


So it look like a problem with the implementation of Number Formats in Conditional formatting in XL 2007, which has subsequently been fixed in 2010.
 
@Cyril


A Simple Solution, without Macros


In the range K6:AJ6 use the custom number format

[>=30000]dd/mm;[<30000]##


30,000 is some time in 1982


and just use the Conditional Format which can do the colors Ok

This will still allow the following cells to function as normal
 
@Chandoo & @Hui : Many many thanks to both of you. You are opening me new sights.

I've never thought about number formating but this will be great for both 1/ correcting the bug and 2/ improving speed.


I've been largely inspired by Fernando's work for the Gantt diagram and tweetboard, rewriting completely the "calculations" sheets to fit my needs...


>> Note the custom format works well, speeding up the sheet refresh as expected (I had a problem with scrollbars if I click to fast, they repeatedly scroll, now it's OK with a fast PC).


@Hui again : the board was OK in 2003, since I used it (with the same conditionnal format --> see Fernando's) before switching to 2007.
 
You must log in or register to reply here.
Back
Top