Calculate Future Date Excluding Weekends

Hello Again

Please see if you can help once more. I am using the same structure to update another column, under the same sub:

If Target.Column = 5 Then  '
    
     Select Case Range("d69").Value
          Case "CB1W"
              ActiveCell.Value = DateAdd("ww", 1, Now())
          Case "CB2W"
              ActiveCell.Value = DateAdd("ww", 2, Now())
          Case "CB3W"
              ActiveCell.Value = DateAdd("d", 21, Now())
          Case "CB4W"
              ActiveCell.Value = DateAdd("d", 28, Now())
          Case "CB5W"
              ActiveCell.Value = DateAdd("d", 35, Now())
          Case "NI"
              ActiveCell.Value = ""
          Case Else
             ActiveCell.Value = ""
    End Select

I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)

I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.

Thank you very much once again in advance for your help.

Jean

Re: Restrict Event Code to Column

Hi p45cal and shg

Thank you very much both for your inputs. I have made changes as you suggested and both are working great. It makes sense not to use the Now function as I wanted the date entries to be static and not be recalculated. Would that be also the case with Date?

In case Date does recalculate I have used the value from my first post :

DateAdd("w", 2, Range("b1").Value)

Is that the way to do it so that the entries are not recalculated?

I think that I meant using the WORKDAY (rather than WEEKDAY) function in conjunction with DateAdd to exclude weekends.

Thanks again for your help

Jean

Re: Calculate Future Date Excluding Weekends

Thanks Dave

Sorry, I thought that I was continuing from a reply to my post from shg and not realised that it necessitated a new thread.

shg, please ignore my earlier comments as I thought that the values in the Active cell would be recalculated every time I reopened the spreadsheet which it doesn't. Sorry.

With regards to excluding weekends I found this function at http://www.utteraccess.com/forums/access/access1436228.html which looks like what I want but I can't seem to make it work for me, maybe because it's designed for Access:

Public Function AddWorkdays(dteStart As Date, intNumDays As Integer) As Date


Dim dteCurrDate As Date
Dim intWeekEnd As Integer
Dim i As Integer
Dim intWeekday As Integer


Select Case intNumDays
    Case 48
        intWeekEnd = 4


    Case 60
        intWeekEnd = 5


    Case 90
        AddWorkdays = DateAdd("d", 90, dteStart)
        GoTo Exit_AddWorkDays


End Select


dteCurrDate = dteStart
AddWorkdays = dteStart
i = 1
Do While i < intNumDays


    intWeekday = WeekDay(dteCurrDate, vbMonday)
    If WeekDay(dteCurrDate, vbMonday) <= intWeekEnd Then
        i = i + 1
    End If
        
    dteCurrDate = dteCurrDate + 1


Loop
AddWorkdays = dteCurrDate


Exit_AddWorkDays:
End Function

I have changed the first case statement as follows:
Case 7
intWeekEnd = 1

The code for 5 days from today (monday 27 august in Brisbane , Australia) :

If Target.Column = 5 Then  '
    
     Select Case Target.Offset(0, -1).Value
        Case "CB5"
              ActiveCell.Value = DateAdd("d", 7, AddWorkdays(Date), 7)
     End Select

The function returns Sat 1 Sep 07.

Any help would be greatly appreciated.

Jean

Re: Calculate Future Date Excluding Weekends

Hi filippo

Was wondering why workday would not work in the code. Things are so simple when you know how. I thought that it was only available in the formula bar. I've referenced ATPVBAEN.XLA in the vbe.

Thanks very much - it seems that you have to work in multiples of 5 with workday ie to add a year to today's date - adding 365 days to today's date results in Mon 19 Jan 09 whereas adding 260 results in Mon 25 Aug 08.

Thanks for your help. You've solved my problem in one swift swoop.

Jean