Advanced Conditional Formatting in Excel

[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…