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