Help PLEASE...Schedule Delivery Date excluding Weekends

[sahadevreddy]

Hello,

Need your help in getting a formula for the below issue.

I need to provide the schedule end date based on the lead time.In the below table, The numbers under the column Number are fixed and numbers in the Schedule Delivery are based on the Lead time.In this case Lead time is 1 so the items shipped on Monday(referring 2) will deliver on Tuesday(so the number next to Monday in the Scheduled delivery is 3.For the items shipped on Friday will ship on Monday.I need a formula in the Scheduled delivery column which returns the value of the day(Number) on which delivery happens, this should exclude weekends if the delivery falls on weekends.Please do the needful.

Day	Number	Schedule Delivery	Lead Time
Monday	2	3	1
Tuesday	3	4
Wednesday	4	5
Thursday	5	6
Friday	6	2

<tbody>
</tbody>

 

[liveinhope]

Do you need to use this particular format ? If you can swap over to a date-based system you can use WORKDAYS . (and this will also allow for public holidays, company shutdown days,etc)
list of in dates in A
forumua in B =WORKDAY(A2,$C$2,$E$2:$E$13)
C2 =leadtime
E2:E?? = list of holidays

in	out	leadtime		hols
24/02/2014	26/02/14	2		27/02/2014
25/02/2014	28/02/14			18/04/2014
26/02/2014	3/03/14
27/02/2014	3/03/14
28/02/2014	4/03/14
1/03/2014	4/03/14
2/03/2014	4/03/14
3/03/2014	5/03/14
4/03/2014	6/03/14
5/03/2014	7/03/14
6/03/2014	10/03/14
7/03/2014	11/03/14
9/04/2014	11/04/14
10/04/2014	14/04/14
11/04/2014	15/04/14
12/04/2014	15/04/14
13/04/2014	15/04/14
14/04/2014	16/04/14
15/04/2014	17/04/14
16/04/2014	21/04/14
17/04/2014	22/04/14
18/04/2014	22/04/14
19/04/2014	22/04/14
20/04/2014	22/04/14

<COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY>
</TBODY>

 

[sahadevreddy]

Hello Sir,

Thank you very much for the reply and the suggestion.

However I am looking for the format that I have mentioned.Please do the needful.

Regards,
Saha.

Do you need to use this particular format ? If you can swap over to a date-based system you can use WORKDAYS . (and this will also allow for public holidays, company shutdown days,etc)
list of in dates in A
forumua in B =WORKDAY(A2,$C$2,$E$2:$E$13)
C2 =leadtime
E2:E?? = list of holidays

in	out	leadtime		hols
24/02/2014	26/02/14	2		27/02/2014
25/02/2014	28/02/14			18/04/2014
26/02/2014	3/03/14
27/02/2014	3/03/14
28/02/2014	4/03/14
1/03/2014	4/03/14
2/03/2014	4/03/14
3/03/2014	5/03/14
4/03/2014	6/03/14
5/03/2014	7/03/14
6/03/2014	10/03/14
7/03/2014	11/03/14
9/04/2014	11/04/14
10/04/2014	14/04/14
11/04/2014	15/04/14
12/04/2014	15/04/14
13/04/2014	15/04/14
14/04/2014	16/04/14
15/04/2014	17/04/14
16/04/2014	21/04/14
17/04/2014	22/04/14
18/04/2014	22/04/14
19/04/2014	22/04/14
20/04/2014	22/04/14

<tbody>
</tbody>

 

[liveinhope]

ok formula in C2 =IF(MOD(B2+$D$2,7)< B2,MOD(B2+$D$2,7)+2,MOD(B2+$D$2,7))

NB 1 that this only works if max schedule days is 5 - so what is your max days
NB2 - does not adjust for holidays/company shutdown days