Advanced Conditional Formatting in Excel? 2

[LBryant777]

I produce a daily timeline report that reads from dates and times from another worksheet in the report. The formula that I use to create my ‘timebar’

=IF(AND(E$1>=($C2-TIME(0,0,1)),E$1<=ROUND($D2*24/0.25,0)*(0.25/24)),"P","")

Row 1 holds the value for each hour of the days I am currently working on.
Col C holds the start date/time of the corresponding record; Col D, the end date/time. I usually only deal with a max of 150 records at one time.

If the timeframe fits the criteria of the formula, it will put a ‘P’ in the cell.

Therefore, an example of what my timebars may look like is:

P
P
P
P
P P P P P P P P P P P
P P P P P
P
P
P
P
P P
…with each line of ‘P’s representing a timebar in the timeline.


Now, I have several questions:

1) My formula only calculates on the hour, not the half hour or quarter hour. How can I modify it?
2) I know I can use conditional formatting to change the shading and borders. However, borders will surround individual cells and not the ‘timebar’. Is that possible to modify in the current conditional formatting dialog box?
3) The biggest issue is that all conflicts in columns must be shaded red, while non-conflicts are to be shaded green. Is there a way to write a conditional loop to have it loop through the ‘timeline’ region and cause two different colors of shading based on conflict/non-conflict?

Hopefully, I have explained myself well enough to get some help. All responses are appreciated!

Regards…

 

[SkipVought]

LBryant777,

1) Interval: I would create a NAMED Range -- mine is named T_Int for Time Interval. In that cell =1/24 would be a one hour interval, =1/24/2 would be half hour interval.

2) Values in E1 to whatever: I guess that E1 contains the starting Date & Time. Then F1 =E1+T_Int copied across. Now you have you time intervals. Your formula need not change

3) Conditional Formatting:
select ALL cells with formula
CF Formula - Cell Value Is, Equal to, P -- format with Border ABOVE & BELOW (you could use 2 more Conditions to format the Vertical ENDS, but why not use the remaining 2 to format the RED/GREEN conditions that you never defined as to WHAT the CONFLICT CRITERIA is???)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I think I figgured out what you wanted

1) Conditional Formatting: Selecting all formula cells, E1 in Upper-left
[tt]
Condition1 - Formula is: =AND(COUNTIF(E$2:E$999,"P")>1,E2="P") - Format Pattern RED
Condition2 - Formula is: =AND(COUNTIF(E$2:E$999,"P")=1,E2="P") - Format Pattern GREEN
[/tt]
where E$2:E$999 is the COLUMN range of formulas.

Using this approch, why worry about BORDERS???

2) Your Formula: Modify using T_Inc
[tt]
=IF(AND(E$1>=($C2-TIME(0,0,1)),E$1<=ROUND($D2/T_Inc,0)*T_Inc),"P","")
[/tt]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JenJohnson]

Skip,

I was looking for exactly how to do this, and your directions couldn't be clearer. Star for you!

JenJohnson

 

[LBryant777]

I see why you were voted TipMaster of the Week! Well, you get my vote again for next week - thanks a million!!!!