Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
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" wrote in message ... 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/ |
#3
|
|||
|
|||
=A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5))
where A1 is the date, A2 is the addition -- HTH Bob Phillips "pankaj_zen" wrote in message ... 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/ |
#4
|
|||
|
|||
Hi Bob,
Today + 12 gives Thurday the 9th of June, instead of the next day. Mangesh "Bob Phillips" wrote in message ... =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)) where A1 is the date, A2 is the addition -- HTH Bob Phillips "pankaj_zen" wrote in message ... 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/ |
#5
|
|||
|
|||
On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen
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 |
#6
|
|||
|
|||
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" wrote in message ... Hi Bob, Today + 12 gives Thurday the 9th of June, instead of the next day. Mangesh "Bob Phillips" wrote in message ... =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)) where A1 is the date, A2 is the addition -- HTH Bob Phillips "pankaj_zen" wrote in message ... 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/ |
#7
|
|||
|
|||
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 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 |
#8
|
|||
|
|||
On Fri, 27 May 2005 14:29:48 +0100, "Bob Phillips"
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 |
#9
|
|||
|
|||
On Fri, 27 May 2005 08:40:28 -0400, Ron Rosenfeld
wrote: On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen 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 |
#10
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... Hi Bob, Today + 12 gives Thurday the 9th of June, instead of the next day. Mangesh "Bob Phillips" wrote in message ... =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)) where A1 is the date, A2 is the addition -- HTH Bob Phillips "pankaj_zen" wrote in message ... 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/ |
#11
|
|||
|
|||
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+SI GN(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" wrote in message ... 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" wrote in message ... 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" wrote in message ... Hi Bob, Today + 12 gives Thurday the 9th of June, instead of the next day. Mangesh "Bob Phillips" wrote in message ... =A1+A2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A1+A2)))=5)) where A1 is the date, A2 is the addition -- HTH Bob Phillips "pankaj_zen" wrote in message ... 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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Month | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) |