Posted By

mprabhuram on 06/15/13


Tagged

excel formula


Versions (?)

Time Based Gantt Chart in Excel


 / Published in: Visual Basic
 

URL: 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.

  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

Report this snippet  

You need to login to post a comment.