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

 

[Vidar]

Hi

If you have Excel 2010 this should work. If you have earlier versions we need another approach.
First, put the dates of your holidays (excluded sundays) in a range and name that range "Holidays".
In cell D2 use:
=WORKDAY.INTL(E2,-6,11,Holidays)
In F2 use:
=WORKDAY.INTL(E2,C2,11,Holidays)
In G2 use:
=WORKDAY.INTL(E2,1,11,Holidays)

Vidar

 

[proficient]

Hi

If you have Excel 2010 this should work. If you have earlier versions we need another approach.
First, put the dates of your holidays (excluded sundays) in a range and name that range "Holidays".
In cell D2 use:
=WORKDAY.INTL(E2,-6,11,Holidays)
In F2 use:
=WORKDAY.INTL(E2,C2,11,Holidays)
In G2 use:
=WORKDAY.INTL(E2,1,11,Holidays)

Vidar

Vidar I am using excel 2007 kindly tell other formulas... thanks

 

[Special-K99]

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

Tip 2 and possibly 3

 

[proficient]

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

Tip 2 and possibly 3

Dear Special-K99,

Thanks for your attention, I had posted the same question a month ago, but did not get any single reply yet, that is why I have posted the same question again with above mentioned subject... So kindly help me out, I am waiting for your positive post related response.

Regards,

Kamran Mumtaz

 

[proficient]

Bump!

 

[proficient]

Still waiting for the reply...

 

[Michael M]

Kamran
Have a look here for for a method that should solve your problem



A Better NETWORKDAYS

 

[proficient]

Thanks Michael M for your response, I have seen the link but this is not what I want...

 

[proficient]

Excel Workbook
	A	B	C	D	E	F
1	STITCHING PLAN
2
3	Cut Qty.	Capacity	Days Reuired	Input Date	1st Output Date	Last Output Date
4	15000	800	19	26-Oct-12	01-Nov-12	22-Nov-12
5	10000	800	13	17-Nov-12	23-Nov-12	07-Dec-12
6	4000	800	5	02-Dec-12	08-Dec-12	13-Dec-12
Sheet1

VBA CODE




Option Explicit
Option Compare Text

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' EDaysOfWeek
' Days of the week to exclude. This is a bit-field
' enum, so that its values can be added or OR'd
' together to specify more than one day. E.g,.
' to exclude Tuesday and Saturday, use
' (Tuesday+Saturday), or (Tuesday OR Saturday)
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Enum EDaysOfWeek
Sunday = 1 ' 2 ^ (vbSunday - 1)
Monday = 2 ' 2 ^ (vbMonday - 1)
Tuesday = 4 ' 2 ^ (vbTuesday - 1)
Wednesday = 8 ' 2 ^ (vbWednesday - 1)
Thursday = 16 ' 2 ^ (vbThursday - 1)
Friday = 32 ' 2 ^ (vbFriday - 1)
Saturday = 64 ' 2 ^ (vbSaturday - 1)
End Enum

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workday2
' This is a replacement for the ATP WORKDAY function. It
' expands on WORKDAY by allowing you to specify any number
' of days of the week to exclude.
' StartDate The date on which the period starts.
' DaysRequired The number of workdays to include
' in the period.
' ExcludeDOW The sum of the values in EDaysOfWeek
' to exclude. E..g, to exclude Tuesday
' and Saturday, pass Tuesday+Saturday in
' this parameter.
' Holidays an array or range of dates to exclude
' from the period.
' RESULT: A date that is DaysRequired past
' StartDate, excluding holidays and
' excluded days of the week.
' Because it is possible that combinations of holidays and
' excluded days of the week could make an end date impossible
' to determine (e.g., exclude all days of the week), the latest
' date that will be calculated is StartDate + (10 * DaysRequired).
' This limit is controlled by the RunawayLoopControl variable.
' If DaysRequired is less than zero, the result is #VALUE. If
' the RunawayLoopControl value is exceeded, the result is #VALUE.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function Workday2(StartDate As Date, DaysRequired As Long, _
ExcludeDOW As EDaysOfWeek, Optional Holidays As Variant) As Variant

Dim N As Long ' generic counter
Dim C As Long ' days actually worked
Dim TestDate As Date ' incrementing date
Dim HNdx As Long ' holidays index
Dim CurDOW As EDaysOfWeek ' day of week of TestDate
Dim IsHoliday As Boolean ' is TestDate a holiday?
Dim RunawayLoopControl As Long ' prevent infinite looping
Dim V As Variant ' For Each loop variable for Holidays.

If DaysRequired < 0 Then
' day required must be greater than or equal
' to zero.
Workday2 = CVErr(xlErrValue)
Exit Function
ElseIf DaysRequired = 0 Then
Workday2 = StartDate
Exit Function
End If

If ExcludeDOW >= (Sunday + Monday + Tuesday + Wednesday + _
Thursday + Friday + Saturday) Then
' all days of week excluded. get out with error.
Workday2 = CVErr(xlErrValue)
Exit Function
End If

' this prevents an infinite loop which is possible
' under certain circumstances.
RunawayLoopControl = DaysRequired * 10
N = 0
C = 0
' loop until the number of actual days worked (C)
' is equal to the specified DaysRequired.
Do Until C = DaysRequired
N = N + 1
TestDate = StartDate + N
CurDOW = 2 ^ (Weekday(TestDate) - 1)
If (CurDOW And ExcludeDOW) = 0 Then
' not excluded day of week. continue.
IsHoliday = False
' test for holidays
If IsMissing(Holidays) = False Then
For Each V In Holidays
If V = TestDate Then
IsHoliday = True
' TestDate is a holiday. get out and
' don't count it.
Exit For
End If
Next V
End If
If IsHoliday = False Then
' TestDate is not a holiday. Include the date.
C = C + 1
End If
End If
If N > RunawayLoopControl Then
' out of control loop. get out with #VALUE
Workday2 = CVErr(xlErrValue)
Exit Function
End If
Loop
' return the result
Workday2 = StartDate + N

End Function




I am using this formula and VBA code but in Input column "D4:D6" the formula is not working properly and returning Sunday.
If we minus the value more than one then it does not work properly, kindly tell me where I am doing wrong, OR if other wise suggest me a better formula to exclude Sundays...