Either or?

dch5876

New Member
Joined
Mar 17, 2017
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
try

Book1
ABCD
160 Calendar Day Notice15 Business Day Notice5 Business Day NoticeEstimated Delivery Date
26/3/20198/2/2019
36/1/20197/16/20198/6/2019
45/21/20197/5/20197/26/20198/2/2019
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
Maybe...

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

M.
 
Upvote 0
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.)
 
Upvote 0
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)))
 
Upvote 0
Scott,\
This worked exactly as needed. Greatly appreciate your quick response.

try
ABCD
60 Calendar Day Notice15 Business Day Notice5 Business Day NoticeEstimated 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]
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top