Either or?

[dch5876]

I am tracking dates and want to calculate the estimated delivery date in a single cell as I populate dates in each milestone.

So I have a 60 day(E), 15 day(F), 5 day(G) notification column. I record the date I receive these notices in their respective column. I want to create an estimated delivery date column (H) based on the most recent date.

Example
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]60 Calendar Day Notice[/TD]
[TD]15 Business Day Notice[/TD]
[TD]5 Business Day Notice[/TD]
[TD]Estimated Delivery Date[/TD]
[/TR]
[TR]
[TD]2019-06-03[/TD]
[TD][/TD]
[TD][/TD]
[TD]2019-08-02[/TD]
[/TR]
[TR]
[TD]2019-06-01[/TD]
[TD]2019-07-16[/TD]
[TD][/TD]
[TD]2019-08-06[/TD]
[/TR]
[TR]
[TD]2019-05-21[/TD]
[TD]2019-07-05[/TD]
[TD]2019-07-26[/TD]
[TD]2019-08-02[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

try

Book1
	A	B	C	D
1	60 Calendar Day Notice	15 Business Day Notice	5 Business Day Notice	Estimated Delivery Date
2	6/3/2019			8/2/2019
3	6/1/2019	7/16/2019		8/6/2019
4	5/21/2019	7/5/2019	7/26/2019	8/2/2019
Sheet1

Cell Formulas
Range	Formula
D2	{=IF(MATCH(1,IF(ISNUMBER(A2:C2),1))=1,A2+60,IF(MATCH(1,IF(ISNUMBER(A2:C2),1))=2,WORKDAY.INTL(B2,15),WORKDAY.INTL(C2,5)))}
D3	{=IF(MATCH(1,IF(ISNUMBER(A3:C3),1))=1,A3+60,IF(MATCH(1,IF(ISNUMBER(A3:C3),1))=2,WORKDAY.INTL(B3,15),WORKDAY.INTL(C3,5)))}
D4	{=IF(MATCH(1,IF(ISNUMBER(A4:C4),1))=1,A4+60,IF(MATCH(1,IF(ISNUMBER(A4:C4),1))=2,WORKDAY.INTL(B4,15),WORKDAY.INTL(C4,5)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Maybe...

H2
=IF(COUNT(E2:G2)=1,E2+60,WORKDAY(INDEX(E2:G2,COUNT(E2:G2)),CHOOSE(COUNT(E2:G2)-1,15,5)))

M.

 

[BlueHornet]

Well ... =IF( MAX( E2:G2) = 0, "", MAX( E2:G2))

But I don't see where you're getting your current Estimated date from, so there may be another data field to consider. (My formula won't return your results.)

 

[Norie]

Perhaps.

=IF(MATCH(0,A2:C2,-1)=1,A2+60,WORKDAY(INDEX(A2:C2,MATCH(0,A2:C2,-1)),CHOOSE(MATCH(0,A2:C2,-1)-1,15,5)))

 

[dch5876]

Scott,\
This worked exactly as needed. Greatly appreciate your quick response.

try

	A	B	C	D
60 Calendar Day Notice	15 Business Day Notice	5 Business Day Notice	Estimated Delivery Date

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/3/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/2/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/1/2019[/TD]
[TD="align: right"]7/16/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/6/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5/21/2019[/TD]
[TD="align: right"]7/5/2019[/TD]
[TD="align: right"]7/26/2019[/TD]
[TD="align: right"]8/2/2019[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IF(MATCH(1,IF(ISNUMBER(A2:C2),1))=1,A2+60,IF(MATCH(1,IF(ISNUMBER(A2:C2),1))=2,WORKDAY.INTL(B2,15),WORKDAY.INTL(C2,5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]{=IF(MATCH(1,IF(ISNUMBER(A3:C3),1))=1,A3+60,IF(MATCH(1,IF(ISNUMBER(A3:C3),1))=2,WORKDAY.INTL(B3,15),WORKDAY.INTL(C3,5)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]{=IF(MATCH(1,IF(ISNUMBER(A4:C4),1))=1,A4+60,IF(MATCH(1,IF(ISNUMBER(A4:C4),1))=2,WORKDAY.INTL(B4,15),WORKDAY.INTL(C4,5)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[dch5876]

BlueHornet,
Someone is submitting to me notifications of delivery at specific intervals (60day, 15day 5 day). But when I record them I want the estimated delivery date to update based on the date they submit their notification. Scott T.'s worked exactly as needed. Appreciate your comment.

 

[dch5876]

Marcelo,
appreciate the post.

 

[dch5876]

Thanks Norie!