Excel Conditional Formatting

[#1]

Hopefully, this needs just a quick answer.

I am trying to use Conditional Formatting in Excel for the first time ever. I have a spreadsheet in which I need to put a number of times. For those times where they are 1 hour or more, I want them to appear in the format [h]:mm:ss. For those times less than 1 hour, I want the format mm:ss. I have worked out how to do that - format all as [h]:mm:ss, and Conditional Format using the values 'less than', '1:00:00', and the Format 'mm:ss'. Not difficult, not even for me. However, I thought, if I ever would like to have a look at any Conditional Format rules that might exist in an Excel file, how would I do that. Having developed a small test file where the times were as set out above, just to see if I could get Conditional Formatting to work, I then tried Conditional Formatting/Manage Rules. It sort of showed me the rule I had set up, but it showed 'Cell Value<0.41666666667. My question therefore is "how do I get it to show me that the rule is 'Cell Value<1:00:00', rather than the one shown just above?"

Thanks for any help

[#2]

The cell rule you are seeing is based on the way that Excel stores date times in an x.y format where the x is the number of days before its epoch and the y is the fractional portion of the day. However, the value you have of 0.41666666667 when formatted in Excel for Mac as Time shows 10:00:00 so for 1:00:00 I need 0.0416666666666667.

@penneck, I don't think you can have it shown the way you want, but others may well know different.

Edit: Corrected typos.

[#3]

@penneck

I am not entirely clear about the usefullness of what you want to do, but this may help you:

https://stackoverflow.com/questions/15612235/how-to-format-time-from-hhmmss-to-hhmm-in-excel

[#4]

Mook

Yes you are correct - when I typed in the decimal value, I managed to miss out a 0 immediately after the decimal place - mea culpa. However, that is not the point of my post. I have a large spreadsheet with many times in. All time cells were formatted in [h]:mm:ss, but I wanted a rule that would change the format to mm:ss if the time was less than 1 hour - it makes the spreadsheet easier to read. My 'condition' for the rule in question was therefore '<1:00:00'. The rule was working correctly, it was just me being inquisitive, in case I ever produced a rule that didn't work how I expected it to. I expected that if I looked at that rule in Manage Rules, it would have said that the condition was '<1:00:00', which was what I had set. However, when I went into Manage Rules, it didn't state that. It stated '<0.04166666...7'. Because this was the first time I had ever used Conditional Formatting, I might have been misusing Manage Rules. Am I misusing it, or does Manage Rules give misleading information?

[#5]

Excel stores  cell data as a value plus a code which defines the display format. I think it only stores the value as either a numeric, or as a string. 

When computing conditions based on cell value, it only looks at the value, not the format in which it is displayed. And the manage rules only displays the less than value as a simple numeric.

You get the same effect if you create a column of percentages. Format them all as two decimal places (e.g. 23.33%). Then conditionally format them so that any value greater than 20% is formatted to zero decimal places (23%). Even though you input the rule as >20%, the manage rule dialogue shows it as >0.2.

It's not misleading information 0.04166666...7 IS 1:00:00 to Excel, try changing the format on a cell containing 1:00:00 and see what you get. It's just that the manage rules dialogue is showing you what's under the hood so to speak

[#6]

Thanks Wisty. The earlier emails looked like that was the way things were going, but I needed someone to say an equivalent to "it doesn't tell you what the format is", so that I would know I was using the Conditional Format Manage Rules correctly. It's a pity that MS didn't go the whole hog, and include effect of the format.

My thanks also to Mook and DavePlus for their help