Determine date based on request date meanwhile falls on particular days

[Hikari]

I wish to use a formula to determine delivery date based on the request date meanwhile I need it to falls on particular days which allows delivery.
I have been digging around the forum to find relevant formula but I failed to get any. Please advise if you have any suggestion. Thanks~*

Example:

Customer: ABC (Deliver on Thursday).
Req Date: 28 Jun 2013
Del Date: ? --> Outcome should be 27 Jun 2013 as I need the del date to be schedule before the req date & falls on Thurs.

Customer: DEF (Deliver on Monday).
Req Date: 28 Jun 2013
Del Date: ? --> Outcome should be 24 Jun 2013 as I need the del date to be schedule before the req date & falls on Mon.I wish to use a formula to determine delivery date based on the request date meanwhile I need it to falls on particular days which allows delivery.
I have been digging around the forum to find relevant formula but I failed to get any. Please advise if you have any suggestion. Thanks~*

Example:

Customer: ABC (Deliver on Thursday).
Req Date: 28 Jun 2013
Del Date: ? --

[vlady]

look attachment.

Checkdays.xls

[Hikari]

Hi Vlady - Thanks for the quick respond.

It worked for the sample day I mention. However, when I change the request date, it don't quite match what I'm looking for.

Example:

Req date: 21 Aug 2013 (Deliver on Thursday), the delivery date should be schedule before the req date & falls on Thurs means 15 Aug 2013.

However, when I input 21 Aug 2013 in the excel, the delivery date come out as 29 Aug 2013 ( Next week after the req date).

Appreciate if you could solve it. Thanks again

[vlady]

=A2-WEEKDAY(A2,1)-B1
B1 = 2 change accordingly experiment for day here.

[Hikari]

Hi Vlady - Thanks again! (^-^)

Great~! It shows the correct outcome as I change to few dates! Hmm.. I wonder if it can add one more condition?
Example: Req Date: 06 Jun 2013 (Delivery on Thursday). Req Date = Del Date, so, can it remain on 06 Jun 2013?

[vlady]

6/6/2013 will return 5/30/2013 right,
are you saying if it exceeds the month it retains the Req. Date?

[vlady]

=if(month(a2-weekday(a2,1)-b1)<month(a2),a2,a2-weekday(a2,1)-b1)

[Hikari]

I mean some req date co-incidentally falls on the same day as the allowed delivery day. Therefore, no changes needed.

E.g. 06 Jun 2013, it is Thursday. So, it will be req date equals to del date. The req date is co-incidentally is the del date/ day.

I need to put this formula on a long list excel, so, it will be manual work if I have to check one by one to see if req date = del date.

[Hikari]

Hi Vlady - I'm very appreciate your help... But I just discover another error T^T... Do you think it is possible to solve?

E.g. Delivery on Thursday. Req Date: 17 May 2013. The del date should fall on 16 May 2013 but the formula appear as 09 May 2013..

[Hikari]

Hi Vlady - Thanks for the great effort!!! ^^

=if(month(a2-weekday(a2,1)-b1)<month(a2),a2,a2-weekday(a2,1)-b1)

It solves the Req Date = Del Date issue but it can't solve Req Date: 17 May 2013 = Del Date: 16 May 2013 issue.

[daddylonglegs]

Try this version

=A2-7+WEEKDAY(A2+B1+2)

[Hikari]

Hi daddylonglegs - Thanks for the effort. Unfortunately, it is not the result I want.

The formula provided by Vlady almost could solve my needs but it still not yet exactly the result I wish to get.

[daddylonglegs]

OK, yes, that was incorrect but try this version

=A2+1-WEEKDAY(A2-B1+1)

where A2 contains the Req Date and B1 contains the Delivery day number (1 = Sun through to 7 = Sat)

I think that works for all your stated examples - it gives you the delivery day that falls either on A2 itself or the closest day before

[Hikari]

OK, yes, that was incorrect but try this version

=A2+1-WEEKDAY(A2-B1+1)

where A2 contains the Req Date and B1 contains the Delivery day number (1 = Sun through to 7 = Sat)

I think that works for all your stated examples - it gives you the delivery day that falls either on A2 itself or the closest day before

Hi Daddylonglegs

OMG! I can't believe there is a formula to solve these many conditions of my job! I'm very grateful & appreciate your efforts & advise.
Meanwhile, I hope you can give me a brief explanation on the formula you have created as it will be helpful for me to understand