Advanced Search

Time Based Gantt Chart in Excel

/ Published in: Visual Basic
Save to your folder(s)
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 
  1. Note: To do
  2.  
  3. Start   = $L6
  4. Finish  = $M6
  5.  
  6. Current = T$5
  7. Previous= S$5
  8.  
  9.  
  10. Logic
  11. =====
  12. S >= P S <= C    C >= F (obvious) => Run Within
  13. S >= P S <= C	 C <  F (obvious) => Start here
  14. S <  P C >= F	 C >= F P <= F	  => Finish here     
  15. S <  P (obvious) C <  F (obvious) => Passthrough 
  16.  
  17.  
  18. Excel Formula
  19. =============
  20.  
  21. =IFERROR(IF(AND(start >= previous,start <= current, current >= Finish),4,
  22.             IF(AND(start >= previous,start <= current, current < Finish),1,
  23.          	IF(AND(start < previous, previous <= Finish, current >= Finish),2,
  24.                     IF(AND(start < previous, current < Finish),0,""	
  25.                       ) 
  26. 		  )
  27.               )
  28.     	   ),9  	
  29. 	 )
  30.  
  31.  
  32. custom time format : m/d - h:mm AM/PM

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.