Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions User Defined > WORKDAY6< Previous | Next > 
 

WORKDAY6(lStartDate, lDays, rgeHolidays)

 
 

Returns the serial number before or after a given number of days from a start date assuming a 6-day working week.

 

 
lStartDateThe starting date.
lDaysThe number of non weekend and non holiday days before ot after "lStartDate".
rgeHolidaysThe range of cells containing your holidays.
 

 

REMARKS

 
 

  • There is an Array Formula equivalent if you do not want to use the user defined function.

    		 
     

  • This user defined function is similar to the built-in WORKDAY() function.

    		 

     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    Public Function WORKDAY6(ByVal lStartDate As Long, _
                             ByVal lDays As Long, _
                             ByVal rgeHolidays As Range) As Long
    Dim ldate As Long
    Dim idaycount As Long
    Dim bholiday As Boolean
    Dim objcell As Range

       Call Application.Volatile(True)

       If lDays > 0 Then idaycount = idaycount + 1
       If lDays < 0 Then idaycount = idaycount - 1
       Do While (lDays <> 0)
          ldate = lStartDate + idaycount
          bholiday = False
          If WorksheetFunction.Weekday(ldate, 2) < 7 Then
             For Each objcell In rgeHolidays
                If ldate = CLng(objcell.Value) Then
                   bholiday = True
                End If
             Next objcell
             If bholiday = False Then
                If lDays > 0 Then lDays = lDays - 1
                If lDays < 0 Then lDays = lDays + 1
             End If
          End If
          If lDays > 0 Then idaycount = idaycount + 1
          If lDays < 0 Then idaycount = idaycount - 1
       Loop
       WORKDAY6 = lStartDate + idaycount
    End Function
    		   

     

    Example

    		 


     

    Array Formula

    		 
     

    This array formula was originally created by the late Frank Kabel who was an Excel MVP (Microsoft Valued Professional).

    		 
     

    To find out more about Array Formulas please refer to the Array Formulas section.

    		 
     

    This example illustrates how to use an array formula to obtain the required date.

    		 
     

    The table below shows the sequence of dates we are going to use and is just for reference.

    		 
       
     

    The formula in cell "C3" returns the next working day (assuming a 6 day working week) that is 10 days after 25/12/2004.

    		 
     

    This formula must be entered as an array formula using (Ctrl + Shift + Enter).

    		 
       
     

    The named range "Start_Date" refers to cell "C25" which contains your start date.

    		 
     

    The named range "Days" refers to the cell "C26" which contains the number of workdays past the start date which you want to return.

    		 
     

    The named range "Holidays" refers to the cell "C27" which should contain the dates of any additional dates you want to exclude.

    		 
     

    The formula [SIGN(Days)] is used to determine if you want to return a date before or after the start date (i.e. whether to add or subtract the number of days).

    		 
     

    The formula [ROW(INDIRECT("1:"&ABS(Days) ) )] returns an array of numbers from 1 to the number of days before or after.

    		 
     

    This array is then multiplied by 10. This is to allow for all the weekends and possible holidays that might have to be excluded from the series.

    		 

     

    The formula [WEEKDAY(Start_Date+SIGN(Days)*ROW(INDIRECT("1:"&ABS(Days)*10))),2)] returns an array of 1's and 0's indicating if the date is a Sunday.

    		 
     

    Using a return-type of "2" for the WEEKDAY() function any Sundays will be identified by the number 7.

    		 
     

    The formula [MATCH(Start_Date+SIGN(Days)*ROW(INDIRECT("1:"&ABS(Days)*10))),Holidays,0))] creates a similar array of dates and then does a lookup to check if any of these days match the dates in the defined Holidays cell range.

    		 
     

    The MATCH() function will return the position of any dates which are defined as holidays.

    		 
     

    This is then wrapped by a ISNA() function because a "#N/A" is returned when there is no match.

    		 
     

    The two arrays are then multiplied together to get a single array that contains just 1's and 0's.

    		 
     

    The SMALL() function is then used to return the date which is in the correct position from this final array.

    		 
     

    This formula is extremely complicated so you may prefer to use a custom worksheet function instead.

    		 

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >