Re: Conditional Format

From: Max (demechanik_at_yahoo.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 19:28:49 +0800

Perhaps one way ..

Assuming the sample data:

> 5/7/04 16:03 Jon Smith
> 5/10/04 9:02 Raul
> 5/10/04 15:59 Joe Bloke
> 5/11/04 8:57 Ben Over
> 5/11/04 15:59 Smiley Face
> 5/12/04 9:06 Ronaldo
> 5/12/04 15:59 Shaft
> 5/13/04 9:27 Lorna
> 5/13/04 16:12 RVN
> 5/14/04 8:19 Roger

is in A1:A10

Let's split the data first

Select col A
Click Data > Text to columns
("Delimited" will be selected)
Click Next

In step2 of the wiz.:
Check the box for "space" > click Next

In step3 of wiz.:
In the data preview pane: click to select the first col (the dates col)
Select "MDY" from the drop menu under "Col data format"

Click Finish

The original data in col A will be split
into 4 cols, A to D: Date, Time, Name1 & Name2

Now to apply the cond format:

Select A1:D10
(i.e. the entire sample data range)

Click Format > Conditional Formatting

Under Condition 1, make the settings:
Formula Is | =$B1>TIMEVALUE("9:00 AM")
Click Format button > Patterns tab > Light blue? > OK
Click OK at the main dialog

The above should accomplish what you want:
Rows where the time in col B is > 9:00 am
will be coloured light blue 

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"xDeniumx >" <<xDeniumx.1co552@excelforum-nospam.com> wrote in message
news:xDeniumx.1co552@excelforum-nospam.com...
> 5/7/04 16:03 Jon Smith
> 5/10/04 9:02 Raul
> 5/10/04 15:59 Joe Bloke
> 5/11/04 8:57 Ben Over
> 5/11/04 15:59 Smiley Face
> 5/12/04 9:06 Ronaldo
> 5/12/04 15:59 Shaft
> 5/13/04 9:27 Lorna
> 5/13/04 16:12 RVN
> 5/14/04 8:19 Roger
>
>
> I want to do something similar with these data. As you can see, it's
> date and time. What I want to do is to highlight all the cells where
> the time is over 9:00. Can someone help?
>
> Ideally I would have preferred that the name of the people along with
> the time and date to be pasted on another ***, but i'm not sure how
> to do that.    :(
>
>
> NOTE: Time and Date are in the same column, while names are in a
> different column
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>