

Your Free Project Scheduling and Gantt Chart Tool Using Microsoft Excel 
main  introduction  download  help pages  excel vba  useful resources  books  sponsor / advertise  forum  author's profile 
Calculating the Calendar Days (Tasks Duration)To calculate the days between two dates we can simply use the EndDate minus StartDate. However, for project tasks, if we just use the difference between the EndDate and StartDate, we will be short of 1 day, that is the StartDay itself. Therefore, a simple way is to add a "1" to the result to take care of the lack of 1 day in the result. To cater for importing the tasks from Microsoft Project, we can add some additional conditions. Therefore, to calculate the number of Calendar Days for each task, we can use the formula as below: = IF(D5<>"",ROUNDDOWN(E5D5+1,0),0), where E5 is the EndDate and D5 is the StartDate D5<>"" checks if the StartDate cell is empty. If it is not, we then use the formula (EndDate  StartDate + 1) to produce the result. In a normal situation, this is all we need. However, to cater for pasting the StartDate and EndDate from Microsoft Project, we can use the RoundDown() formula to produce a whole number. ROUNDDOWN(value, decimal places) Lastly, "0" in the last segment of the formula will put a "0" as the resultant Calendar Days if the StartDate is empty.



Calculating the Network DaysThe above formula will used in the Calendar Days will include the Weekends and holidays. To calculate the Effort or Workdays excluding Weekends or Holidays (if you maintain a list) we can use the Networkdays formula which is very straight forward. =NETWORKDAYS(D5,E5), where D5 is the EndDate and E5 is the StartDate We can also add a range that consists of a series of holidays to be excluded from the calculations. Example; = NETWORKDAYS(StartDate,EndDate,R1C1,RxC1) The diagram below shows the difference between the Calendar Days and the Networkdays.
