Help PLEASE...Schedule Delivery Date excluding Weekends

S

sahadevreddy

New Member
Joined
Feb 25, 2014
Messages
3
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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Number[/TD]
[TD]Schedule Delivery[/TD]
[TD]Lead Time[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
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

[TABLE="width: 263"]
<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>[TR]
[TD="class: xl65, width: 84, bgcolor: white"]in[/TD]
[TD="width: 64, bgcolor: transparent"]out[/TD]
[TD="width: 64, bgcolor: transparent"]leadtime[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"]hols[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]24/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]26/02/14[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27/02/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]25/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/02/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18/04/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]26/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]27/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]28/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]7/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]11/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]12/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]13/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]14/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]15/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]16/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]17/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]18/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]19/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]20/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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.

liveinhope said:
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

[TABLE="width: 263"]
<tbody>[TR]
[TD="class: xl65, width: 84, bgcolor: white"]in[/TD]
[TD="width: 64, bgcolor: transparent"]out[/TD]
[TD="width: 64, bgcolor: transparent"]leadtime[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"]hols[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]24/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]26/02/14[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27/02/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]25/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/02/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18/04/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]26/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]27/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]28/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]6/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]7/03/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/03/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]11/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]12/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]13/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]14/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]15/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]16/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]17/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]18/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]19/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]20/04/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/04/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
377
bobsan42
bobsan42
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
125
sijpie
sijpie
C
  • Question Question
Dynamic calendar for parenting time
2
Replies
14
Views
991
felixstraube
F
J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
368
Eric W
Eric W

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

Share this page

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