A Gantt chart that spans 24 hrs

[Bill H]

As an exercise, I am trying to adapt the "Tip-of-the-Week" Gantt chart to a 24-hour period.
Anyone know how to get Excel to use times past midnight? (I am using Excel 97)

Assuming:
$E2 - Start Time (8:00 PM)
$F2 - End Time (4:00 AM)
G$1 thru BB$1 - (6:00 AM thru 5:00 AM)
In this list of the 24 hours, 8:00 PM would be in AI$1, and 4:00AM in AY$1.

I have tried the following formulas to trigger the conditional formatting for cell AI2 (where the Gantt line should begin).

Condition 1to begin the Gantt line)
=($E2>$F2)+AI$1=($E2>$F2)+$E2

Condition 2: (to end the Gantt line)
=($E2>$F2)+AI$1=($E2>$F2)+$F2

Condition 3: (the middle of the Gantt line)
=AND(($E2>$F2)+AI$1>$E2,($E2>$F2)+AI$1<$F2)

The problem is that nothing works after midnight, even though I've added ($E2>$F2} to get it to add 24 hours if the start time is greater than the end time.

Any help would be greatly appreciated.
Bill H

[Ian Mac]

I have a good example of a Gantt chart that can easily be converted for Hours:Mins, it's currently Days. Also it actually a chart. If you'd like me to send it I'd be more than happy