Home » Schedule Tasks Using Dependencies

Schedule Tasks Using Dependencies

Today’s challenge is contributed by Abdelrahman Omer, MBA, PMP. List the Plan Start and End dates for all tasks based on the predecessor relationship (Finish-to-Start dependency : task’s start date is determined by the end date of its predecessor) Ex. Task 1 : Plan Start = 1-Nov (Predecessor End Date) Plan End = 1-Nov + 5 (Duration) = 6-Nov Task 2 : Plan Start = 6-Nov (late day between Task1 and Start) Plan End = 6-Nov + 2 (Duration) = 8-Nov

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 618
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Schedule Tasks Using Dependencies with Power Query

Power Query solution 1 for Schedule Tasks Using Dependencies, proposed by Kris Jaganah: 
let
 A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 B = Table.TransformColumns( A ,{"Predecessor " , each if _ = null then "Start" else _ }),
 C = Table.ToColumns( B),
 D = List.Zip( {C{2},C{0}}),
 E = List.Positions(D),
 F = List.Transform( E , (v)=> List.Sort( List.Distinct( List.Accumulate(E , {D{v}{0}} , 
 (x,y)=> x& {List.Select(D, each _ {1} = List.Last( x) ){0}{0}})))),
 G = List.Zip( {C{0} , C{3}}),
 H = List.Transform( E ,(z) => List.Sum( List.TransformMany ( F{z} , each List.Select( G, (x)=> x{0} = _ ) , (v,w)=> w{1} )) ),
 I = List.Transform(E, each [Plan Start = C{1}{0}+ hashtag#duration(H{_},0,0,0) , Plan End = #"Plan Start" + hashtag#duration(C{3}{_},0,0,0) ] ),
 J = Table.FromRecords(I)
in
 J
Power Query solution 2 for Schedule Tasks Using Dependencies, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Seed = Table.AddColumn(Source, "End Date", each [Plan Start] + Duration.From([Duration])), 
  Records = Table.ToRecords(Seed), 
  Generate = List.Accumulate(
    List.Skip(Records), 
    Seed, 
    (s, c) =>
      [
        S1 = c
          & [
            Plan Start = List.First(
              Table.SelectRows(s, (f) => f[Task] = c[#"Predecessor "])[End Date]
            )
          ], 
        S2 = S1 & [End Date = S1[Plan Start] + Duration.From(c[Duration])], 
        S3 = Table.ReplaceMatchingRows(s, {c, S2})
      ][S3]
  ), 
  Return = Generate[[Plan Start], [End Date]]
in
  Return
Power Query solution 3 for Schedule Tasks Using Dependencies, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Accum = List.Accumulate(
    {1 .. Table.RowCount(Source) - 1}, 
    [a = Source{0}, b = {a & [Plan End = Date.AddDays(a[Plan Start], a[Duration])]}][b], 
    (s, c) =>
      [
        u = Source{c}, 
        v = List.Select(s, each [Task] = u[Predecessor]){0}[Plan End], 
        w = u & [Plan Start = v], 
        x = s & {w & [Plan End = Date.AddDays(w[Plan Start], w[Duration])]}
      ][x]
  ), 
  FromRec = Table.FromRecords(Accum)[[Plan Start], [Plan End]], 
  Result = Table.TransformColumnTypes(FromRec, {{"Plan Start", type date}, {"Plan End", type date}})
in
  Result
Power Query solution 4 for Schedule Tasks Using Dependencies, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rec = List.Accumulate(
    {1 .. Table.RowCount(Source) - 1}, 
    Record.AddField(
      [], 
      Source[Task]{0}, 
      [
        Plan Start = Source[Plan Start]{0}, 
        Plan End   = Date.AddDays(#"Plan Start", Source[Duration]{0})
      ]
    ), 
    (a, v) =>
      Record.AddField(
        a, 
        Source[Task]{v}, 
        [
          Plan Start = Record.Field(a, Source[Predecessor]{v})[Plan End], 
          Plan End   = Date.AddDays(#"Plan Start", Source[Duration]{v})
        ]
      )
  ), 
  Res = Table.FromRecords(Record.ToList(Rec))
in
  Res

Solving the challenge of Schedule Tasks Using Dependencies with Excel

Excel solution 1 for Schedule Tasks Using Dependencies, proposed by Bo Rydobon 🇹🇭: 
=REDUCE(
    B3+D3*{0,
    1},
    C4:C12,
    LAMBDA(
        a,
        v,
        VSTACK(
            a,
            INDEX(
                a,
                XMATCH(
                    v,
                    A3:A12
                ),
                2
            )+VLOOKUP(
                v,
                A3:D12,
                4,
                
            )*{0,
            1}
        )
    )
)

=LET(
    z,
    A3:D12,
    REDUCE(
        0,
        INDEX(
            z,
            ,
            3
        ),
        LAMBDA(
            a,
            v,
            LET(
                m,
                IFNA(
                    INDEX(
                        a,
                        XMATCH(
                            v,
                            TAKE(
                                z,
                                ,
                                1
                            )
                        ),
                        2
                    ),
                    B3
                )+{0,
                1}*TAKE(
                    TAKE(
                        z,
                        1
                    ):v,
                    -1,
                    -1
                ),
                IF(
                    v=0,
                    m,
                    VSTACK(
                        a,
                        m
                    )
                )
            )
        )
    )
)
Excel solution 2 for Schedule Tasks Using Dependencies, proposed by John V.: 
=REDUCE(
    B3+C3:D3,
    C4:C12,
    LAMBDA(
        a,
        v,
        VSTACK(
            a,
            XLOOKUP(
                v,
                DROP(
                    A3:v,
                    -1,
                    -2
                ),
                DROP(
                    a,
                    ,
                    1
                )
            )+N(
                TAKE(
                    +v:D12,
                    1
                )
            )
        )
    )
)
Excel solution 3 for Schedule Tasks Using Dependencies, proposed by Kris Jaganah: 
=LET(a,A3:A12,b,C3:C12,c,D3:D12,d,IF(b="","Start",b),e,MAP(a,LAMBDA(z,SUM(XLOOKUP(TEXTSPLIT(LET(w,LAMBDA(ME,x,LET(x,XLOOKUP(TEXTAFTER(x,",",-1,,,x),a,d),IF(RIGHT(x)="t",x,x&","&ME(ME,x)))),w(w,z)),","),a,c))))+B3,HSTACK(e,e+c))
Excel solution 4 for Schedule Tasks Using Dependencies, proposed by Kris Jaganah: 
=LET(
    a,
    A3:A12,
    b,
    C3:C12,
    c,
    D3:D12,
    d,
    IF(
        b="",
        "Start",
        b
    ),
    e,
    MAP(
        a,
        LAMBDA(
            v,
            SUM(
                XLOOKUP(
                    UNIQUE(
                        SCAN(
                            v,
                            SEQUENCE(
                                ROWS(
                                    a
                                )
                            ),
                            LAMBDA(
                                x,
                                y,
                                XLOOKUP(
                                    x,
                                    a,
                                    d
                                )
                            )
                        )
                    ),
                    a,
                    c
                )
            )+B3
        )
    ),
    HSTACK(
        e,
        e+c
    )
)
Excel solution 5 for Schedule Tasks Using Dependencies, proposed by Timothée BLIOT: 
=LET(A,SEQUENCE(9),B,IFNA(--REGEXEXTRACT(C4:C12,"d"),0),C,D4:D12, TAKE(SORT(REDUCE({0,0,0,0,0},UNIQUE(B),LAMBDA(w,v,LET(D,TAKE(FILTER(w,TAKE(w,,1)=v),,-1),VSTACK(w,FILTER(HSTACK(A,B,C,A*0+D,C+D),B=v,"")))))),,-2)+45627)
Excel solution 6 for Schedule Tasks Using Dependencies, proposed by Md. Zohurul Islam: 
=LET(
hdr,HSTACK("Plan Start","PLan End"),
std,B3,
_s1,A3:A12,
_s2,C3:C12,
_s3,D3:D12,
_s4,IF(_s2="","Start",_s2),
_s5,MAP(_s1,LAMBDA(w,LET(a,SCAN(w,SEQUENCE(ROWS(_s1)),LAMBDA(x,y,XLOOKUP(x,_s1,_s4))),b,XLOOKUP(a,_s1,_s3),c,SUM(b)+std,c))),
_s6,_s5+_s3,
_s7,HSTACK(_s5,_s6),
ans,VSTACK(hdr,_s7),
ans)
Excel solution 7 for Schedule Tasks Using Dependencies, proposed by Md. Zohurul Islam: 
= 2 Then
 u = Range(
     "C" & x + 2
 ).Offset(
     -2,
      0
 ).Value
 v = Application.Match(
     u,
      rngPred,
      0
 )
 w = WorksheetFunction.Index(
     rngEnd,
      v,
      1
 )
 PLtart = w + 0
 PLend = PLtart + dr
 ElseIf Right(
     prd,
      1
 ) = 1 And Right(
     prdofset,
      1
 ) <> 1 Then
 u = prd
 v = Application.Match(
     u,
      rngPred,
      0
 ) - 1
 w = WorksheetFunction.Index(
     rngEnd,
      v,
      1
 )
 PLtart = w + 0
 PLend = PLtart + dr
 Else
 u = prd
 v = Application.Match(
     u,
      rngPred,
      0
 ) - 1
 w = WorksheetFunction.Index(
     rngEnd,
      v,
      1
 )
 PLtart = w + 0
 PLend = PLtart + dr
 End If
 'post result
 Range(
     "F" & x + 2
 ).NumberFormat = "dd-mmm"
 Range(
     "F" & x + 2
 ) = PLtart
 Range(
     "G" & x + 2
 ).NumberFormat = "dd-mmm"
 Range(
     "G" & x + 2
 )
Excel solution 8 for Schedule Tasks Using Dependencies, proposed by Pieter de B.: 
=REDUCE(
    B3+D3*{0,
    1},
    C4:C12,
    LAMBDA(
        x,
        y,
        VSTACK(
            x,
            INDEX(
                x,
                XMATCH(
                    y,
                    A3:A12
                ),
                2
            )+INDEX(
                D4:D12,
                ROW(
                    y
                )-3
            )*{0,
            1}
        )
    )
)
Excel solution 9 for Schedule Tasks Using Dependencies, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=LET(c,LET(i,MAP(B3:B15,C3:C15,LAMBDA(m,n,IF(m<>"",m,FILTER((MAP(B3:B15,C3:C15,LAMBDA(a,b,IF(a<>"",a,FILTER((MAP(B3:B15,C3:C15,LAMBDA(i,j,IF(i<>"",i,FILTER(MAP(B3:B15,C3:C15,LAMBDA(a,b,IF(a<>"",a,FILTER(IF(C3:C15="",IF(B3:B15<>"",B3:B15,MAP(C3:C15,LAMBDA(a,FILTER(B3:B15,a=A3:A15))))+D3:D15,MAP(C3:C15,D3:D15,LAMBDA(x,y,FILTER(B3:B15,x=A3:A15)+y))),b=A3:A15))))+D3:D15,j=A3:A15)))))+D3:D15,b=A3:A15)))))+D3:D15,n=A3:A15)))),HSTACK(i,i+D3:D15)),IF(A3:A15="End",MAX(c),c))

Solving the challenge of Schedule Tasks Using Dependencies with Python

Python solution 1 for Schedule Tasks Using Dependencies, proposed by Konrad Gryczan, PhD: 
import pandas as pd
from datetime import timedelta
path = "618 Project Plan with Relationship.xlsx"
input = pd.read_excel(path, usecols="A:E", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=10)
input['Start'] = input.apply(lambda row: row['Plan Start'] if row['Task'] == 'Start' else pd.NaT, axis=1)
input['End'] = input['Start'] + input['Duration'].apply(lambda d: timedelta(days=d))
predecessors = input['Predecessor '].dropna().unique()
for pred in predecessors:
 pred_end = input.loc[input['Task'] == pred, 'End'].values[0]
 input.loc[input['Predecessor '] == pred, 'Start'] = pred_end
 input['End'] = input['Start'] + input['Duration'].apply(lambda d: timedelta(days=d))
result = input[['Start', 'End']].reset_index(drop=True)
test.columns = result.columns
print(all(result == test)) # True

Solving the challenge of Schedule Tasks Using Dependencies with Python in Excel

Python in Excel solution 1 for Schedule Tasks Using Dependencies, proposed by Alejandro Campos: 
from datetime import datetime, timedelta
df, start_date, task_dates = xl("A2:D12", headers=True), datetime(2024, 12, 1), {}
for _, t in df.iterrows():
 ps = start_date if not t["Predecessor"] else task_dates[t["Predecessor"]]["plan_end"]
 task_dates[t["Task"]] = {"plan_start": ps, "plan_end": ps + timedelta(days=t["Duration"])}
result_df = pd.DataFrame([
 {k: task_dates[t["Task"]][k.replace(" ", "_").lower()].strftime('%d-%b') for k in ["Plan Start", "Plan End"]}
 for _, t in df.iterrows()
])
result_df

Solving the challenge of Schedule Tasks Using Dependencies with R

R solution 1 for Schedule Tasks Using Dependencies, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/618 Project Plan with Relationship.xlsx"
input = read_excel(path, range = "A2:D12")
test = read_excel(path, range = "F2:G12")
 
input = input %>%
 mutate(
 Start = if_else(Task == "Start", `Plan Start`, as.Date(NA)),
 End = if_else(Task == "Start", `Plan Start` + days(Duration), as.Date(NA))
 )
predecessors = unique(input$Predecessor[!is.na(input$Predecessor)])
for (pred in predecessors) {
 input = input %>%
 mutate(
 Start = case_when(
 Predecessor == pred ~ input$End[input$Task == pred],
 !is.na(Start) ~ Start,
 TRUE ~ as.Date(NA)
 ),
 End = case_when(
 Predecessor == pred ~ Start + days(Duration),
 !is.na(End) ~ End,
 TRUE ~ as.Date(NA)
 )
 )
}
 
all.equal(test, input %>% select(Start, End), check.attributes = FALSE)
# [1] TRUE

Solving the challenge of Schedule Tasks Using Dependencies with Excel VBA

Excel VBA solution 1 for Schedule Tasks Using Dependencies, proposed by Md. Zohurul Islam: 
Sub ExcelChallenge618()
 Dim nx, x, u, v, w
 Dim std, prd, prdofset
 Dim dr, drofset
 Dim PLtart, PLend
 Dim rngPred As Range, rngEnd As Range
 
 Range("F2:G2") = Array("PlanStart", "Plan End")
 
 nx = WorksheetFunction.CountA(Range("D3:D10000"))
 Set rngPred = Range("C3:C10000")
 Set rngEnd = Range("G3:G10000")
 std = Range("B3").Value
 
 For x = 1 To nx
 prd = Range("C" & x + 2).Value
 prdofset = Range("C" & x + 2).Offset(-1, 0).Value
 dr = Range("D" & x + 2).Value
 drofset = Range("G" & x + 2).Offset(-1, 0).Value
 'condition 01
 If prd = "" And dr = 0 Then
 PLtart = std
 PLend = std
 
 ElseIf prd = "Start" And dr > 0 Then
 PLtart = std
 PLend = std + dr
 
 ElseIf Right(prd, 1) = 1 And prdofset = "Start" Then
 PLtart = drofset
 PLend = PLtart + dr

&&&

Leave a Reply