Challange for all experts

[proficient]

Assalam-o-alaikum and Hi to every one, I am in trouble from last one week just because of this planning sheet and I think its also a challenge for you guys, the Challenge is that I am making a planning sheet and in this I dont want sundays and other holidays in my planning and also want to make a column where I put holidays except sundays, which will not include in my planning, this is the sample data







[TABLE="width: 1054"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cut Qty[/TD]
[TD][/TD]
[TD]Daily Capacity[/TD]
[TD][/TD]
[TD]Days Required To Stitch[/TD]
[TD][/TD]
[TD]Input Date[/TD]
[TD][/TD]
[TD]1st Output Date[/TD]
[TD][/TD]
[TD]Last Output Date[/TD]
[TD][/TD]
[TD]Dispatch Start Date[/TD]
[TD][/TD]
[TD]Dispatch End Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]15000[/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[TD]19[/TD]
[TD][/TD]
[TD]4-Nov-12[/TD]
[TD][/TD]
[TD]10-Nov-12[/TD]
[TD][/TD]
[TD]29-Nov-12[/TD]
[TD][/TD]
[TD]11-Nov-12[/TD]
[TD][/TD]
[TD]30-Nov-12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12000[/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]24-Nov-12[/TD]
[TD][/TD]
[TD]30-Nov-12[/TD]
[TD][/TD]
[TD]15-Dec-12[/TD]
[TD][/TD]
[TD]1-Dec-12[/TD]
[TD][/TD]
[TD]16-Dec-12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD]10-Dec-12[/TD]
[TD][/TD]
[TD]16-Dec-12[/TD]
[TD][/TD]
[TD]10-Jan-13[/TD]
[TD][/TD]
[TD]17-Dec-12[/TD]
[TD][/TD]
[TD]11-Jan-13[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 454"]
<tbody>[TR]
[TD]1)[/TD]
[TD="colspan: 6"]in C2 I have putted this formula =ROUND(A2/B2+.25,0)[/TD]
[/TR]
[TR]
[TD]2)[/TD]
[TD="colspan: 4"]IN E2 I manually putted the Start date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


now I want formula for following cells

1st Formula In cell D2: The formula should minus 6 days from 1st Input date E2 which I had putted manually, and also avoid Sundays OR other holidays.

2nd Formula In Cell F2: The formula should add 19 days in 1st output, you can see the 19 days in C2… also don not want Sundays and holidays between in 19 days

3rd Formula In G2: The Formula should add 1 day in 1st output date and avoid Sundays OR other holidays.

I hope I am making sense... I am waiting for your prompt response, Thanks in advance

 

[proficient]

Somebody help me please...

 

[proficient]

I think no one have time to reply and resolve my problem...

 

[Vidar]

Hi
If you can do without the holiday part, then try this:

In cell D2 use:
=CHOOSE(WEEKDAY(E2,2),-1,-1,-1,-1,-1,-1,0)+E2-6
In F2 use:
=CHOOSE(WEEKDAY(E2,2),3,3,3,3,3,4,3)+E2+19
In G2 use:
=CHOOSE(WEEKDAY(E2,2),0,0,0,0,0,1,0)+E2+1

Vidar

 

[proficient]

Thanks Vidar for your response... But your given formula minus 7 days rather than 6 and if I put 5 instead of 6 formula returns Sundays... More attention required...

 

[Vidar]

Hi
Ok, try this:
=CHOOSE(WEEKDAY(E2,2),-1,-1,-1,-1,-1,0,0)+E2-5

 

[proficient]

Thanks Vidar, Formula is working But Holidays Column is still required for Planning sheet... Do something dear please...

 

[Vidar]

I'm sorry, maybe someone else might help you further with this one.
You have to check if one of the listed holidays falls on a sunday. If not, you might subtract or add it twice.
It's a easy solution if you get Excel 2010.

 

[proficient]

Hi Vidar, after a long time came with same problem could you please elucidate this formula for my easy understanding...
=CHOOSE(WEEKDAY(E2,2),-1,-1,-1,-1,-1,0,0)+E2-5

 

[Vidar]

Hi Vidar, after a long time came with same problem could you please elucidate this formula for my easy understanding...
=CHOOSE(WEEKDAY(E2,2),-1,-1,-1,-1,-1,0,0)+E2-5

1. The function WEEKDAY(E2,2) returns the weekday ( 1 to 7) of the date in E2. The second argument (2) returns 1 for monday, 2 for tuesday, ... and 7 for sunday.
2. The CHOOSE() function uses the weekday number as the first argument and uses that number to pick it's second argument. If the weekday number is from
1 to 5, it chooses "-1", if the weekday is 6 or 7, it chooses "0".
3. Then it takes that number and adds it to the date in E2 (-1+E2 or 0+E2). It either subtracts on day from the date in E2 or not (-1 or 0).
4. Lastly, 5 more days are subtracted from that date.

 

[proficient]

Hi Vidar, I am using your given formula "=WORKDAY.INTL(E2,-6,11,Holidays)" (playing with Excel 2010 in office) but getting an extra day in my plan, here is the data and result below for your ease.

A1: Start date (1-Dec)
B1: Day for completion (5 days)
F1:F5: holidays (5-Dec only)

Furthermore, using 11 in weekend as there is only Sunday in our country

getting answer in B1: 8-Dec, it should be 7th Dec from 1-Dec.