Jump Thursdays in a month

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?


--
pankaj_zen
------------------------------------------------------------------------
pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
View this thread: http://www.officehelp.in/showthread.php?t=650962
Visit - http://www.officehelp.in | http://www.officehelp.in/index/index.php/

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






"pankaj_zen" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> pankaj_zen
> ------------------------------------------------------------------------
> pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
> View this thread: http://www.officehelp.in/showthread.php?t=650962
> Visit - http://www.officehelp.in |
http://www.officehelp.in/index/index.php/
>

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

where A1 is the date, A2 is the addition

--
HTH

Bob Phillips

"pankaj_zen" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> pankaj_zen
> ------------------------------------------------------------------------
> pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
> View this thread: http://www.officehelp.in/showthread.php?t=650962
> Visit - http://www.officehelp.in |
http://www.officehelp.in/index/index.php/
>

Hi Bob,

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

Mangesh




"Bob Phillips" <[email protected]> wrote in message
news:#[email protected]...
> =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))
>
> where A1 is the date, A2 is the addition
>
> --
> HTH
>
> Bob Phillips
>
> "pankaj_zen" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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?
> >
> >
> > --
> > pankaj_zen
> > ------------------------------------------------------------------------
> > pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
> > View this thread: http://www.officehelp.in/showthread.php?t=650962
> > Visit - http://www.officehelp.in |
> http://www.officehelp.in/index/index.php/
> >
>
>

On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen
<[email protected]> wrote:

>
>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?


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

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

"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Today + 12 gives Thurday the 9th of June, instead of the next day.
>
> Mangesh
>
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:#[email protected]...
> > =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))
> >
> > where A1 is the date, A2 is the addition
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "pankaj_zen" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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?
> > >
> > >
> > > --
> > > pankaj_zen
> >
> ------------------------------------------------------------------------
> > > pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
> > > View this thread: http://www.officehelp.in/showthread.php?t=650962
> > > Visit - http://www.officehelp.in |
> > http://www.officehelp.in/index/index.php/
> > >
> >
> >
>
>

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..



"Ron Rosenfeld" wrote:

> On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen
> <[email protected]> wrote:
>
> >
> >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?
>
>
> 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
>

On Fri, 27 May 2005 14:29:48 +0100, "Bob Phillips" <[email protected]>
wrote:

>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

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

On Fri, 27 May 2005 08:40:28 -0400, Ron Rosenfeld <[email protected]>
wrote:

>On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen
><[email protected]> wrote:
>
>>
>>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?
>
>
>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

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

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




"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > Today + 12 gives Thurday the 9th of June, instead of the next day.
> >
> > Mangesh
> >
> >
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:#[email protected]...
> > > =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))
> > >
> > > where A1 is the date, A2 is the addition
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "pankaj_zen" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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?
> > > >
> > > >
> > > > --
> > > > pankaj_zen
> > >
> > ------------------------------------------------------------------------
> > > > pankaj_zen's Profile: http://www.officehelp.in/member.php?userid=42
> > > > View this thread: http://www.officehelp.in/showthread.php?t=650962
> > > > Visit - http://www.officehelp.in |
> > > http://www.officehelp.in/index/index.php/
> > > >
> > >
> > >
> >
> >
>
>

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



"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Mangesh Yadav" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > Today + 12 gives Thurday the 9th of June, instead of the next day.
> > >
> > > Mangesh
> > >
> > >
> > >
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:#[email protected]...
> > > > =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))
> > > >
> > > > where A1 is the date, A2 is the addition
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "pankaj_zen" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > 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?
> > > > >
> > > > >
> > > > > --
> > > > > pankaj_zen
> > > >
> >
> ------------------------------------------------------------------------
> > > > > pankaj_zen's Profile:
http://www.officehelp.in/member.php?userid=42
> > > > > View this thread: http://www.officehelp.in/showthread.php?t=650962
> > > > > Visit - http://www.officehelp.in |
> > > > http://www.officehelp.in/index/index.php/
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>