Brian Barker wrote:
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
03022008 1306 GMT-6
Thanks Brian. I didnt make it clear - sorry about that.
Let me clarify
Day Date Time - value, value
I use this with my clients to track their progress.
For each date there might be anywhere from 5 to 50 values. Each value is
accounted for by time.
I rearranged the sheet so it reads Time Date Dat Value Value and then
sorted by time. I color coded but at that point - you are stuck at a
point where you cant get the values back into the same order as they
were before. You can order the columns by Date Time Date Value Value and
then sort by date but the times arent correct. But then again - it wont
matter as the cells that are color coded wont maintain the color after
the next sort.
I think what might be easier is to just dump it all into a mysql db and
write some code to extract it the way I want it.
Thanks.
Wade
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
