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
XL-EasyGantt

 

Calculating the Calendar Days (Tasks Duration)

To calculate the days between two dates we can simply use the End-Date minus Start-Date. However, for project tasks, if we just use the difference between the End-Date and Start-Date, we will be short of 1 day, that is the Start-Day 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(E5-D5+1,0),0), where E5 is the End-Date and D5 is the Start-Date

D5<>"" checks if the Start-Date cell is empty. If it is not, we then use the formula

(End-Date - Start-Date + 1) to produce the result.

In a normal situation, this is all we need. However, to cater for pasting the Start-Date and End-Date 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 Start-Date is empty.

 

More Formulas

formula for chart plotting

calculating calendardays

calculating networkdays

highlighting weekends

conditional formatting

 
 

Calculating the Network Days

The 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 End-Date and E5 is the Start-Date

We can also add a range that consists of a series of holidays to be excluded from the calculations.

Example;

= NETWORKDAYS(Start-Date,End-Date,R1C1,RxC1)

The diagram below shows the difference between the Calendar Days and the Networkdays.

Next: Highlighting Weekends>>