Gantt Chart

The internet is filled with gantt chart samples, here is one form my side.

Image

 

So whats new in it, well all the excel based gantt charts I came across used two different rows to show the budget and actual progress,

whereas here with the use of conditional formatting budget/actual progress are shown in same row. Here are the steps to build it

1. write budget and actual start and end dates activity wise

2. on left side calculate duration of each activity

3. further left calculate delay or early on start and end of each activity, conditional format it to highlight any delay

4. write dates in top row beside End of Actual and format them to vertical alignment, further format cells for lower column width 

5. Conditional format (using format only the cell that contain option) the top row of chart area with pattern for budget activity and color for actual activity, as shown in figure below

Image

 

 

6. copy the format to rest of chart area

7. Do cosmetics as you like.

link to file

https://drive.google.com/file/d/0B2FLZ9YjqpJTWmFvbnJITllSNEU/edit?usp=sharing

Number of days between the start and end dates

After reading the heading, the first formula pops up in mind is

=end date – start date + 1

but this post refer to ModelOff 2013 “Excel Golf” – 2013 Finals Group Challenge heat 1   http://www.modeloff.com/questions/

the question : Given the row start and end date, write a single formula to count the number of days between the start and end dates (inclusive) within each period.

Image

the competition required to solve it in 10 mins with shortest possible length, although I took more time than that, infact much more, but the more important thing is solution. It may not be the best or shortest but pretty simple.

the first formula i made was

=MAX(IF($F18<H$17,$F18-H$16+1,H$17-MAX($E18,H$16)+1),0)

it checks if end date is less than the period end date, if yes it subtracts the end date from period start date, else period end date is subtracted from the max of period start date or just the start date. To get rid of negative results I use the max function.

This formula was correct for the start and end date falling in different period, however incorrect for the start and end date falling in same period, so the formula lengthened to

=MAX(IF(AND($E18>=H$16,$F18<=H$17),$F18-$E18+1,IF($F18<H$17,$F18-H$16+1,H$17-MAX($E18,H$16)+1)),0)

the first IF function checks if both the dates are in same period than it gives the difference of the start and end dates

and hence the formula.

the link to question file is given for your perusal

length of my formula is 98, i m waiting for the answer file to check the shortest formula…

till then ciao

[EDIT]

Howdy people, i found a shorter formula, way shorter formula for the question

=MAX(0,MIN(H$17,$F18)-MAX(H$16,$E18)+1)

length 39