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

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  	
	 )

http://www.robertmcquaig.com/blog/solutions/excel-gantt-chart/

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.

Time Based Gantt Chart in Excel

excel

Visual Basic