format cell / rows by highlighting automatically

[CUBALIBRAS]

is there a way to format a row based on cntent. I have 3000 rows which consist of weekday / date / time columns. I want to highlight 16:00 each day and every Sunday but I can't seem to do it using conditional formatting.

any advice appreciated

 

[SkipVought]

Hi,

Please post some sample data.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[AnotherHiggins]

Let's say your data looks like this:
[tt]
A B C
1 weekday date time
2 Sun 7/1/2007 1:00
3 Mon 7/2/2007 2:00
4 Tue 7/3/2007 3:00
5 Wed 7/4/2007 4:00
6 Thu 7/5/2007 1:00
7 Fri 7/6/2007 2:00
8 Sat 7/7/2007 3:00
9 Sun 7/8/2007 4:00[/tt]

You can highlight all Sundays and all 4 o'clocks like this:

[ul][li]Select entire range (columns A:C, or however many columns you use)[/li]
[li]Go to Format > Conditional Format[/li]
[li]Change the first box to Formula is[/li]
[li]In the second box, type in [COLOR=blue white]=TEXT($B1,"DDD")="Sun"[/color][/li]
[ul][li](this assumes that you have all rows selected and a cell in row 1 activated)[/li][/ul]
[li]Select the Format button[/li]
[li]Go to the Patterns tab and select the color you want[/li]
[li]Click OK[/li]
[li]Select Add[/li]
[li]Change the first box to Formula is[/li]
[li]In the second box, type in [COLOR=blue white]=TEXT($C1,"hh:mm")="04:00"[/color][/li]
[li]Select the Format button[/li]
[li]Go to the Patterns tab and select the color you want[/li]
[li]Click OK[/li]
[li]Click OK[/li]
[/ul]

If you select different colors for each criteria and a record meets multiple criteria (like row 9 in the example data above), the row will be formatted according to the first criteria met.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[CUBALIBRAS]

" DAY DATE TIME (IB)(PST) (EST) OPEN

Friday 07/13/07 19:30 12:30 15:30 1.3786
Friday 07/13/07 19:00 12:00 15:00 1.3787
Friday 07/13/07 18:30 11:30 14:30 1.3787
Friday 07/13/07 18:00 11:00 14:00 1.3789
Friday 07/13/07 17:30 10:30 13:30 1.3789

 

[SkipVought]

I got a CF using
[tt]
=OR($C2=0.75,MOD($B2,7)=1)
[/tt]
where column C is TIME and column B is Date.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[CUBALIBRAS]

thanks guys, thats brilliant, saves me a lot of work !!

 

[yogia]

Hi CubaLibras:

In line with Hohn and Skip's thinking ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Actually the DAY colum is REDUNDANT and UNNECESSARY

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[CUBALIBRAS]

I know it's seems odd but I am tracking data every 30 minutes and analyzing it for any patterns that repeat from 16:00 to 15:30 EST next day so in this case it is necessary to see the day as something may occur on the 2nd Tusday of each month between 02:00 and 04:00, or the 4 day of each month at the same time but will be a different day.

This way the results are fewer to analyze so although it looks like overkill it's very handy.

Thank you all for your help, believe me it is greatly appreciated and I'm sure once I get into this a bit more you'll see me again !!

 

[SkipVought]

"...it is necessary to see the day ..."
[tt]
1 date time
2 Sun 7/1/2007 1:00
3 Mon 7/2/2007 2:00
4 Tue 7/3/2007 3:00
5 Wed 7/4/2007 4:00
6 Thu 7/5/2007 1:00
7 Fri 7/6/2007 2:00
8 Sat 7/7/2007 3:00
9 Sun 7/8/2007 4:00
[/tt]
As I stated, the DAY column is both REDUNDANT and USELESS.


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[CUBALIBRAS]

but if the day column is useless and i want to analyze data for every thursday in april how would i do it without the DAY column ?

 

[yogia]

but if the day column is useless and i want to analyze data for every thursday in april how would i do it without the DAY column ?

Hi CUBALIBRAS:

Consider using ...

=IF(WEEKDAY(DateRange)=5,…


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[CUBALIBRAS]

Thanks for your input yogia, but i don't know how to implement it. If you could proide an example that would be great.

regards,

 

[SkipVought]

You need to read and understand this...

faq68-5827

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}