Excel if the day is a monday tuesday wednesday move to next monday. if thursday, friday move to the following monday

[Rami912]

Hello,

I need help setting a formula for lead time in transit. The receipt date always must fall on a Monday. So when someone ships on a monday, tuesday, wednesday then the dock date should equal to the following monday and if the ship date is thursday or friday then the dock date must fall on the next monday

example:

ship date: 11/07/2016 to 11/09/2016 = 11/14/2016 but if ship date 11/10/2016 to 11/11/2016 - 11/21/2016 dock date

Thank you,

RA

 

[Jonmo1]

Which version of Excel are you using?

 

[Jonmo1]

If XL2010+ try

=WORKDAY.INTL(A1,1,"1101111")+5

 

[Rami912]

2007

 

[Rami912]

I also have excel 2013 and it worked. Thank you. I was just not ready for change from 2007 to 2013

 

[Jonmo1]

Ok, try this instead

=A1+MATCH("Wed",TEXT(A1+{1,2,3,4,5,6,7},"ddd"),0)+5

 

[Tetra201]

I think it should be

=A1+MATCH("Wed",TEXT(A1+{0,1,2,3,4,5,6},"ddd"),0)+4

to satisfy 11/9/2016 --> 11/14/2016

 

[Jonmo1]

Ah I misread it, good catch.
I did Monday+Tuesday ships following monday, Wed+Thu+Fri ships the next monday..

But would also have to change Wed to Thu


So they should actually be
=WORKDAY.INTL(A1,1,"1110111")+4
and
=A1+MATCH("Thu",TEXT(A1+{1,2,3,4,5,6,7},"ddd"),0)+4

 

[Rami912]

Wednesday is kind of open for either first or second monday but either one works. Thanks everyone for all your help

 

[Jonmo1]

You're welcome.