Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Function.
hi,
50% of by hair loss this year is due to problems with Analyst tool pack. syncing with excel on several computers i came up with whis formula in excel where FERIADOS = array of dates =SE(F60;SE(OU(DIA.DA.SEMANA(F6)=7;DIA.DA.SEMANA(F 6)=1;ÉNÚM(CORRESP(F6;FERIADOS;0)));workday((F6); 2;FERIADOS);workday((F6);1;FERIADOS));0) translating =IF(F60;IF(OR(WEEKDAY(F6)=7;WEEKDAY(F6)=1;ISNUMBE R(MACH(F6;FERIADOS;0)));workday((F6);2;FERIADOS);w orkday((F6);1;FERIADOS));0) I would like some help on making it in VBA without needing Analyst tool pack so the logic's of this F6 = date 1) if F6 is Saturday, TRUE 2) if F6 is Sunday, TRUE 3) If 6 belongs to Holiday array, TRUE if true then date + 2 days skipping holidays else date + 1 days skipping holidays my biggest problem is what the "workday" formula, do any help is appreciate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Function.
Paulo, What are you trying to do exactly? I used a special formula to calculate the number of days between two dates to include saturdays as a normal workday. I also used a list of holiday's so to exlude these dates from being calculated in. Maybe I can help. Katie Here is a sample code I used from another site: Function NWD(StartDate As Date, EndDate As Date, Holiday As Date, _ Optional Holidays As Date, _ Optional WeekendDay_1 As Integer = 1, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0, _ Optional WeekendDay_4 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 Dim Holiday As Date 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, WeekendDay_4 Count = Count - 1 Case Else If DoHolidays Then If IsNumeric(Application.Match(i, Holidays, 0)) Then _ Count = Count - 1 End If End Select DoHolidays = _ Date = Holiday If StartDate Or EndDate = Holiday _ Then Count -Sum(Holiday) End If SD = StartDate: ED = EndDate If SD ED Then SD = EndDate: ED = StartDate NegCount = True End If Next i If NegCount = True Then Count = -Count NWD = Count End Function -- butrfly_kis3 ------------------------------------------------------------------------ butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23764 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Function.
thank you katie,
I will take a look @ it. "butrfly_kis3" wrote: Paulo, What are you trying to do exactly? I used a special formula to calculate the number of days between two dates to include saturdays as a normal workday. I also used a list of holiday's so to exlude these dates from being calculated in. Maybe I can help. Katie Here is a sample code I used from another site: Function NWD(StartDate As Date, EndDate As Date, Holiday As Date, _ Optional Holidays As Date, _ Optional WeekendDay_1 As Integer = 1, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0, _ Optional WeekendDay_4 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 Dim Holiday As Date 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, WeekendDay_4 Count = Count - 1 Case Else If DoHolidays Then If IsNumeric(Application.Match(i, Holidays, 0)) Then _ Count = Count - 1 End If End Select DoHolidays = _ Date = Holiday If StartDate Or EndDate = Holiday _ Then Count -Sum(Holiday) End If SD = StartDate: ED = EndDate If SD ED Then SD = EndDate: ED = StartDate NegCount = True End If Next i If NegCount = True Then Count = -Count NWD = Count End Function -- butrfly_kis3 ------------------------------------------------------------------------ butrfly_kis3's Profile: http://www.thecodecage.com/forumz/member.php?userid=8 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23764 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |