Formula for date calculation

[SAN04]

Please help me to calculate dates in the yellow cells based on duration in red highlighted.

Few things to consider:
1. Date for Step 1 will be calculated based on the "Start Date" (Start date + Duration for particular step)
2. In case duration is blank, the date should be blank.
3. Every date will follow, the last previous step date when duration is available.
4. If date fall on Friday, Saturday or Holiday, next date should be considered.
5. Need to automate the dates based on identifier in "column B". Based on the change of the identifier date should be changed.
6. A sample output is shown below but automate is needed as more rows will be added after S.no 3.

Appreciate support!

 

[vletm]

This sample will give same as Your Output.
You knows - what do those 4 .. 6 could do or do You?

 

[SAN04]

Thanks Vletm but need automation in regards of identifier.

If i change the identifier (Column B), it should automatically change the dates based on the duration wrt particular identifier in table highlighted in red.

Also, if dates are coming on friday, Saturday it should reflect next date.

 

[vletm]

As I wrote above ..
4) Which dates are You writing?
... eg cell D15 and J16 is Sunday ... so what?
... which Holidays?
5) What is connection between .... B-column ... and Your Output ... now?
> I need clear rules.

 

[SAN04]

Vletm,

4) I put dates as sample (in Output table) with simple calculation and hence asked for automation in case date comes on Friday and Saturday it should write next day in the cell (where you have already written formula). Holiday (Saturday & Sunday)

5) B column will be changed and either of 3 options will be selected but not in same sequence. Sequence will be changed and accordingly from the red highlighted table the duration will be selected, and dates will be calculated.

Also, right now table is up to s.no. 3 but that will be extended to s.no. 200 and identifier will be random (out of 3).

 

[vletm]

4) Even Your sample Output have to be valid.
5) I cannot figure anything, what should I modify.
Also) more rows ... so? Randoms are interesting.

 

[SAN04]

Vletm
4. Output is sample
5. Also random means that s no 1 can have either of 3 identifier and s.no 3 can be further roll down to atleast 200

 

[vletm]

4) ... have to be valid.
5) I cannot figure anything, what should I modify.

 

[p45cal]

In the attached:
Your Table1 with an added row (S No. 4)
Your Table2; with this table it's important to retain the trailing space in the first column header Durations . The rest of the step names can be anything and any number of steps.
Note that these tables are now proper Excel tables.
After adjusting Table2 and Table1, go to the table at cell A18, right-click anywhwere in that table and choose Refresh.
That's it.