Project Based Template For Dates

[kumara_faith]

Hi,

I have the following table:

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.) --><table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2"><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Task Name</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Duration (Days)</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Start Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>End Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Precedence</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>% Completion</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Status</b></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 1</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">42197</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">=WORKDAY(D3,C3,K3:K11)</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 2</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">5</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">42202</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">=WORKDAY(D4,C4,K4:K12)</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 1</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 3</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">42197</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">=WORKDAY(D5,C5,K5:K13)</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr></table>

I am trying to replicate MS Project where if Task 2 has a dependence on Task 1, meaning Task 2 can only start when Task 1 has been completed, then the start date and end date should populate automatically. Task 3 does not have any dependence and therefore when the start date is entered, the end date will automatically populate. The formula needs to exclude weekends and public holidays.

I managed to work out the weekends and public holidays part but I cant figure out how to make the dates populate based on the dependencies.

Any help will be greatly appreciated.

 

[Oeldere]

Maybe like this.

Book1
	A	B	C	D	E	F	G	H
1	Task Name	Duration (Days)	Start Date	End Date	Precedence	% Completion	Status	RESULT
2	Task 1	4	42197	=WORKDAY(D3,C3,K3:K11)
3	Task 2	5	42202	=WORKDAY(D4,C4,K4:K12)	Task 1			=WORKDAY(D3,C3,K3:K11)
4	Task 3	3	42197	=WORKDAY(D5,C5,K5:K13)
Blad17

Cell Formulas
Range	Formula
H2	=IF(E2="","",INDEX($A$2:$G$4,MATCH(E2,$A$2:$A$4,0),4))
H3	=IF(E3="","",INDEX($A$2:$G$4,MATCH(E3,$A$2:$A$4,0),4))
H4	=IF(E4="","",INDEX($A$2:$G$4,MATCH(E4,$A$2:$A$4,0),4))

 

[kumara_faith]

Hi,

My apologies if I was not clear earlier. I need the formula to be in the start date cell where if Task 2 has a dependency on Task 1, then the start date for task 2 will automatically be after the end date of task 1 as below. In the example below, the first task usually will not have any dependency. Task 2 however has a dependency on Task 1. Therefore the end date for Task 1 is 7/16/2015. Since Task 2 can only start after the completion of Task 1, therefore the start date for Task 1 is 7/17/2015. How do I get the start date cell to change accordingly ? In addition, the start date for Task 2 has to be the day after the end date of Task 1 which is 7/17/2015 and not 7/16/2015.

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.) --><table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2"><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Task Name</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Duration (Days)</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Start Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>End Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Precedence</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>% Completion</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Status</b></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 1</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">4</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/12/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/16/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 2</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">5</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/17/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/24/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 1</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr><tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Task 3</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/12/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">7/15/2015</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr></table>

 

[kumara_faith]

Hi,

I have a cross post here:

Project Based Template For Dates

 

[kumara_faith]

Hi,

Sorry to bump this. Any possible solution is greatly appreciated.