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

60 Calendar Day Notice	15 Business Day Notice	5 Business Day Notice	Estimated Delivery Date
2019-06-03			2019-08-02
2019-06-01	2019-07-16		2019-08-06
2019-05-21	2019-07-05	2019-07-26	2019-08-02

<tbody>
</tbody>

 

[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
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

<tbody>
</tbody>

Sheet1​

Array Formulas Cell 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)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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!