At 17:55 01/03/2008 -0600, Wade Smart wrote:
I have a column that I have been painfully editing by hand. The
column has times recorded like : 0723, 1046, 1755, and so on. I'm
color coding the times by hand:
1 = 0500 - 1000
2 = 1000 - 1400
3 = 1400 - 1800
4 = 1800 - 2000
5 = 2000 - 2200
Is there a way that I can automate this?
There are two techniques that suggests themselves (neither if which
quite do what you need):
o Go to Format | Cells | Numbers. Enter conditional formatting in
the "Format code" window. For example:
[<0.41667][RED]HHMM;[<0.58333][GREEN]HHMM;[BLUE]HHMM
This will give your first category red text and your second green,
whilst later times will be blue. (The figures included represent
10:00 and 14:00 - each expressed as a fraction of a day.) The
problem here is twofold. You can control only the text colour and
not other aspects of formatting, such as background colour. And you
can set only two conditions and consequently three outcomes, whereas
you need at least four and five respectively.
o Go to Format | Conditional Formatting... . Here you can set more
complicated conditions. You can either set a (limited) condition on
the cell value itself, or else refer to the logical value of a
formula in another cell, which can be arbitrarily complicated, of
course. Since with this technique you apply the formatting using
cell styles, you have a wider choice of formatting styles, including
background colour. But you are still limited, I think, this time to
three conditions and four outcomes - less than you need.
Your question is slightly ambiguous. The above (unsuccessful)
techniques are directed at creating a spreadsheet where you could
enter values and they would assume the colour markup
automatically. But you may have just the simpler task of marking up
an existing, fixed list. If this is so, there may be a technique
which could save you time:
o If necessary, fill a column parallel to your values with numbers in order.
o Use Data | Sort... to sort your data so that the times are in
order, ensuring that you carry along any associated columns (so that
you do not lose the association) and your new column of numbers.
o You can now easily select each of your time segments as
consecutive blocks of values and then apply the relevant formatting
only once for each of your five time intervals.
o Now use Data | Sort... on the whole block again, this time sorting
by the extra column you inserted, so as to return your data to its
original order and position. For this sort, it is important that you
have "Include formats" ticked on the Options tab of the Sort
dialogue. With this tick, the formatting you have applied will move
back along with the values; without the tick, the values would return
but the formatting would stay in place - which is not what you want.
In fact, even if you needed to add to or modify your time data later,
it might still be quicker and more reliable to use this technique to
apply the necessary formatting.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
