Solved Check & set the start date after another task end in excel

May 20, 2016 at 00:21:02
Specs: Windows 7, i5 2520M / 8 GB
I have a schedule control Excel sheet, such as below


Item Task Start End "predecessor task"
1 Gather requirement 2016/5/3 2016/5/9
2 UI Design 2016/5/6 2016/5/22
3 Coding 2016/5/18 2016/6/20
4 Unit tests 2016/5/11 2016/5/18
5 System test 2016/5/18 2016/6/1
6 Beta 1 2016/5/30 2016/5/31
7 Bug fixes 2016/6/3 2016/6/26 5
8 Improvements 2016/6/17 2016/7/6
9 Final testing 2016/7/2 2016/7/10
10 UAT 2016/7/1 2016/7/25 7
11 Release version 2016/7/26 2016/8/4

some task need to start after another task, such task 7 start after task 5 finished, task 10 start after task 7 finished. if I input predecessor 5 at the task 7, then the start date of task 7 need to set the task end date + 1 if the start date of task 7 <= the end date of task 5.

how to write the EXCEL function or VBA to resolve this issue.

if the start date of task 7 changed, the following start date of tasks changed if these task have predecessor setting and need to changed if need.that is the start date and end date of task 7 changed, the task 10 may changed , if the end date of task 7 > start date of task 10.

please assist me, thanks!!


See More: Check & set the start date after another task end in excel

Report •

#1
May 20, 2016 at 07:28:11
What are the "predecessor task" for all of the tasks?
Do they have a set sequence?

Why can't you just list them in the sequence that needs to be followed?

Also, Excel questions are best asked in the Office Software forum.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
May 20, 2016 at 08:52:02
Have you considered using Microsoft Project, or one of the many alternatives? They're pretty much designed for this kind of work.

How To Ask Questions The Smart Way


Report •

#3
May 20, 2016 at 09:44:50
✔ Best Answer
With your data like this:

     A            B               C           D            E
 1) Item    Task               Start        End      predecessor task
 2) 1     Gather requirement 05/03/2016   05/09/2016      
 3) 2     UI Design          05/06/2016   05/22/2016      
 4) 3     Coding             05/18/2016   06/20/2016      
 5) 4     Unit tests         05/11/2016   05/18/2016      
 6) 5     System test        05/18/2016   06/01/2016      
 7) 6     Beta 1             05/30/2016   05/31/2016      
 8) 7     Bug fixes          06/02/2016   06/26/2016      5
 9) 8     Improvements       06/17/2016   07/06/2016      
10) 9     Final testing      07/02/2016   07/10/2016      
11) 10    UAT                06/27/2016   07/25/2016      7
12) 11    Release version    07/26/2016   08/04/2016      

This formula, for Item #7, in cell C8 will get you what your looking for:

=IF(E8="",DATE(2016,6,3),VLOOKUP(E8,$A$2:$D$12,4,0)+1)

This formula, for Item #10, in cell C11 will get you what your looking for:

=IF(E11="",DATE(2016,7,1),VLOOKUP(E11,$A$2:$D$12,4,0)+1)

Problem with both formulas is that you need to Hard Code a default
date into each formula, or you will need to put a default date
somewhere else and do a lookup to get the default date.

If possible, another way, would be to allocate a number of days to
each task, then the End date is dependent on the number of days,
and the Start Date of the next item is the End Date +1 of the previous
task. Something like:

     A            B               C            D              E
 1) Item    Task               Start     Days Allocated      End      
 2) 1    Gather requirement  05/03/2016        6          05/09/2016 
 3) 2    UI Design           05/10/2016       12          05/22/2016

This approach is more linear, but may not be sutiable for your project.

Also, if you do a search, there are many Timeline project templates
on line that you may find suitable. Here is one site:

https://www.smartsheet.com/top-proj...

Also MS has free Excel templates.

There is also the Macro option, but unfortunately, my Macro skills are just above nil.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question