If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Custom Function basic
=WORKDAY(A1,7)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. Joe_Germany wrote: ya.. I got it in add-in.s.. thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
#12
|
|||
|
|||
Custom Function basic
Thanks especially to Toppers & Bob..
Thats true.. What I wanted was the WORKDAY fn.. Since I started making this I thought I would finish this.. So I did.. Adv from WORKDAY ----------------------------- It takes weekend input better.. DisAdv frm WORKDAY ----------------------------- It doesnt add -ve values.. So here it is.. ------------------------ Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) If (WeekDay_St = 7) Then WeekDay_St = 6 End If n_Weekends = WorksheetFunction.Ceiling((WeekDay_St + n - 1) / 5, 1) - 1 n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------------------------------ Now I have to see how I can make it in such a way that it can be added as ADD-INS.. Thx again guys.. Joe Bob Phillips wrote: =WORKDAY(A1,7) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. Joe_Germany wrote: ya.. I got it in add-in.s.. thx.. Joe_Germany wrote: Thanks Toppers.. But where can I find that function.. Is that an Add-on? I dont see it in mine.. Thanks Bob for that.. Let me correct.. Bob Phillips wrote: It also counts too many if you input a sunday date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Joe_Germany" wrote in message ups.com... Thax Toppers.. I got the result I wanted.. My FIRST custom function.. Im happy.. See if it is usefull.. I made this to add a number of working days on a given day.. --------- Function AddWorkDays(Date_St As Date, n As Integer) As Date Dim WeekDay_St, n_Weekends, n_Shift As Integer WeekDay_St = WorksheetFunction.WeekDay(Date_St, 2) n_Weekends = WorksheetFunction.Floor((WeekDay_St + n - 2) / 5, 1) n_Shift = n_Weekends * 2 + n - 1 AddWorkDays = Date_St + n_Shift End Function ------ say A1 is the start date and A2 is the no of working day needed.. A1: 31/08/06 A2: 5 AddWorkDays(A1,A2) will give you 06/09/06 if A2 is 1, its the same day.. If A1 is a weekend and if A2 is 1, the out put is next monday.. Hope someone can use it.. suggessions / comments are most welcome Bye. Joe Toppers wrote: Yes ....in English .... MsgBox Application.Round(123.467, 2) Look in VBA help for all functions available. HTH "Joe_Germany" wrote: Hello all.. I am trying to make a custom function.. I am using german Excel.. My qn is - Can we use the standard functions inside vb.. For eg, ABRUNDEN(x,y) is the function for Round(x,y) can i use abounden(x,y) as a vb code while making the custom function.. If not is there any way we can access the standard functions while making custom functions? Thanks all Joe |
#13
|
|||
|
|||
Custom Function basic
Just like your proposed AddWorkDays !! bg
Pete Bob Phillips wrote: =WORKDAY(A1,7) "Joe_Germany" wrote: One more qn .. the help for WORKDAY says that the date has to be entered with DATE function.. If i have a date in a cell, how I can use that directly to this fn, rather than extracting year, month and date from that & in turn use that in the DATE function for this.. Thanks a lot.. |
|
Thread Tools | |
Display Modes | |
|
|