Jump Thursdays in a month

P

pankaj_zen

Hi all,
i have a unique problem.
here we have thursdays as week ends ( sat & sundays are working days).
Now what i want to do is in anexcel sheet if i enter say for exampl
enter 24 may which is tuesday in any of the cells & digit 5 in anothe
cell, when i add these two i should get 30 may i.e it should jum
thursday my weekly off.
the number can be any thing from1 to 70.

Can any one please help me in this regard
 
M

Mangesh Yadav

Save this function in a standard module:

Function myWorkDay(myDate As Date, AddDays, LeaveDay)

If AddDays = 0 Then
myWorkDay = myDate
Exit Function
End If

Select Case LeaveDay
Case "Sunday"
LD = 1
Case "Monday"
LD = 2
Case "Tuesday"
LD = 3
Case "Wednesday"
LD = 4
Case "Thursday"
LD = 5
Case "Friday"
LD = 6
Case "Saturday"
LD = 7
End Select

i = 1
Do While i <= AddDays
myWorkDay = myDate + i
If WorksheetFunction.Weekday(myWorkDay) = LD Then
AddDays = AddDays + 1
End If
i = i + 1
Loop

End Function



And use it as follows:
=myWorkDay(A1,B1,"Thursday")

where A1 has your start date, and B1 has the number of days you want to add.

Mangesh
 
B

Bob Phillips

=A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))

where A1 is the date, A2 is the addition
 
M

Mangesh Yadav

Hi Bob,

Today + 12 gives Thurday the 9th of June, instead of the next day.

Mangesh
 
R

Ron Rosenfeld

Hi all,
i have a unique problem.
here we have thursdays as week ends ( sat & sundays are working days).
Now what i want to do is in anexcel sheet if i enter say for example
enter 24 may which is tuesday in any of the cells & digit 5 in another
cell, when i add these two i should get 30 may i.e it should jump
thursday my weekly off.
the number can be any thing from1 to 70.

Can any one please help me in this regard?
Click to expand...


Here is a UDF that, with the correct arguments, will treat Thursday as your
weekend.

To enter it, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
below code into the window that opens.

To use it, enter the formula in the form of:

=Wrkday(A1, A2,,5)

Where A1 contains the date and A2 contains the number of days to add.
The blank ( ,, ) is an optional list of holidays which can be another range.
'5' represents Thursday as you can see from the comments in the udf.

====================================
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop

WrkDay = TempDate
End Function
====================================


--ron
 
B

Bob Phillips

Hi Mangesh,

Thanks for the catch. Simplest solution is

=A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))+(--WEEKDAY(A1+
A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)))=5)

Bob
 
T

TomHinkle

This is going to sound neandrolithic (is that a word), BUT in a production
environment it works.

Make a tab listing of all workdays, in sequential order.. It may take you 30
-60 minutes to get a years worth of dates in the list and then to delete
appropriate 'weekends' and holidays.

Then when you do your function, use your custom list of working days. It
will start on your given workday, and simply count down the number of working
days you give it.

Basic and simple. We've implemented the same method on a database because
no one application's holiday calendar fit our needs..
 
R

Ron Rosenfeld

Hi Mangesh,

Thanks for the catch. Simplest solution is

=A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))+(--WEEKDAY(A1+
A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)))=5)

Bob
Click to expand...

Bob,

Your formula also seems to depend on the starting date NOT being on Thursday.

May 26, 2005 + 1 : should be May 27, 2005. Your formula gives May 28


--ron
 
R

Ron Rosenfeld

Here is a UDF that, with the correct arguments, will treat Thursday as your
weekend.

To enter it, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
below code into the window that opens.

To use it, enter the formula in the form of:

=Wrkday(A1, A2,,5)

Where A1 contains the date and A2 contains the number of days to add.
The blank ( ,, ) is an optional list of holidays which can be another range.
'5' represents Thursday as you can see from the comments in the udf.

====================================
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop

WrkDay = TempDate
End Function
====================================


--ron
Click to expand...

Oops, I forgot part of the UDF. You need to paste in the following:

====================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function

Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) <> Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop

WrkDay = TempDate
End Function
===================================


--ron
 
M

Mangesh Yadav

Hi Bob,

There still is a problem, now at 18 days and then 24, 25.
The problem is that, as you push forward, there is an accumulation further
down which the TO part (in the from:to range)
cannot handle.

Mangesh
 
M

Mangesh Yadav

Hi Bob,

This is from one of the links given by Tom.

=$A$1+IF(B1=0,0,SIGN(B1)*SMALL(IF((WEEKDAY($A$1+SIGN(B1)*(ROW(INDIRECT("1:"&
ABS(B1)*10))),2)<>4)*ISNA(MATCH($A$1+SIGN(B1)*(ROW(INDIRECT("1:"&ABS(B1)*10)
)),0,0)),ROW(INDIRECT("1:"&ABS(B1)*10))),ABS(B1)))

.....array formula


A1 is date
B1 is nos of days

Source: http://excelforum.com/showthread.php?t=376705


Mangesh
 
Top