/ Published in: Visual Basic
I wanted a time based gantt chart in excel. Robert's excel template was very useful. The hourly gantt chart was not useful to visualize my needs. I need something with 10 mins precision to analyse patterns in jobs that are running under 15 mins. So the following is the formula that i used to achive it. Its not perfect and i dont have time for perfect right now. Some time later i will have to get back to this and publish a excel template.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
Note: To do Start = $L6 Finish = $M6 Current = T$5 Previous= S$5 Logic ===== S >= P S <= C C >= F (obvious) => Run Within S >= P S <= C C < F (obvious) => Start here S < P C >= F C >= F P <= F => Finish here S < P (obvious) C < F (obvious) => Passthrough Excel Formula ============= =IFERROR(IF(AND(start >= previous,start <= current, current >= Finish),4, IF(AND(start >= previous,start <= current, current < Finish),1, IF(AND(start < previous, previous <= Finish, current >= Finish),2, IF(AND(start < previous, current < Finish),0,"" ) ) ) ),9 ) custom time format : m/d - h:mm AM/PM
URL: http://www.robertmcquaig.com/blog/solutions/excel-gantt-chart/