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]

Friends please help!

 

[Vidar]

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.

Hi
The help for the WORKDAY.INTL() functions says:
"Returns the serial number of the date before or after a specified number of workdays"
So it gives in your example the 5th workday after the start date which results in 08.12.

 

[NinadM]

Hi Kamran,

Try the following formulas and check if this is works for you: (PLEASE NOTE: all the Dates inputted in Column E should not be a SUNDAY or a HOLIDAY)

1st Formula In cell D2:
=E2-(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E2&":"&E2-6)),2)>6)+0)+COUNTIFS(Holidays,">="&E2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E2&":"&E2-6)),2)>6)+0)-6,Holidays,"<="&E2)+6)


2nd Formula In Cell F2:
=SMALL(IF(WEEKDAY(E2+ROW($1:$100))<>1,IF(COUNTIF(Holidays,E2+ROW($1:$100))=0,E2+ROW($1:$100),1E+100),1E+100),C2)


3rd Formula In G2:
=SMALL(IF(WEEKDAY(E2+ROW($1:$100))<>1,IF(COUNTIF(Holidays,E2+ROW($1:$100))=0,E2+ROW($1:$100),1E+100),1E+100),1)

 

[NinadM]

Hi Kamran,

Try the following formulas and check if this is works for you: (PLEASE NOTE: all the Dates inputted in Column E should not be a SUNDAY or a HOLIDAY)

1st Formula In cell D2:
=E2-(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E2&":"&E2-6)),2)>6)+0)+COUNTIFS(Holidays,">="&E2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E2&":"&E2-6)),2)>6)+0)-6,Holidays,"<="&E2)+6)


2nd Formula In Cell F2:
=SMALL(IF(WEEKDAY(E2+ROW($1:$100))<>1,IF(COUNTIF(Holidays,E2+ROW($1:$100))=0,E2+ROW($1:$100),1E+100),1E+100),C2)


3rd Formula In G2:
=SMALL(IF(WEEKDAY(E2+ROW($1:$100))<>1,IF(COUNTIF(Holidays,E2+ROW($1:$100))=0,E2+ROW($1:$100),1E+100),1E+100),1)

Forgot to mention; Formula in F2 & G2 are to be entered as an array (Ctrl+Shift+Enter)