Future Date calcuation excluding dates

[qster]

I have a problem regarding excluding certain dates from calculating future dates.

Say I can't use specific months. (i.e. Winter months) and I want to be able to calculate a year from a date, but it doesn't include Dec., Jan. and Feb. So a year from a date (say Sep. 1, 2006) futuring dating it for a year should return Mar. 1, 2008 as it skips out on Dec., Jan. and Feb of 2007. Cant this be done?

Also would I have to create separate codes to account for holidays that fall on a workday? Of if it falls on a weekend and the following Monday is the holiday.

 

[SkipVought]

Hi,

Something like..
[tt]
Where month([MyDate]) not in (1,2,12)
[/tt]


Skip,

_[red][/red]

 

[randy700]

Check this FAQ. You might be able to make some modifications to include the 3 months you want to skip.

Calculate working days between two dates



Randy

 

[CautionMP]

qster,
Here a cross between [tt]DateAdd()[/tt] and Excel's [tt]NetworkDays()[/tt] that will let you calculate excluding whatever months you don't want to count.

[tt]OmitMonthsCSV[/tt] and [tt]HolidaysCSV[/tt] are just like the arguments in [tt]NetworkDays()[/tt], just the values you want to exclude spereated by commas (i.e. Dec, Jan, Feb = "12,1,2").

I included the routine I was using to test it soi you could see my inputs/outputs from testing.

Public Function DateAddCustom(Interval As String, Number As Double, StartDate As Date, Optional OmitMonthsCSV As String, Optional HolidaysCSV As String) As Date
Dim dteEnd As Date
Dim intItem As Integer, intAdj As Integer
Dim strArray() As String

dteEnd = DateAdd(Interval, Number, StartDate)
'Check if there are months to omit
If OmitMonthsCSV <> "" Then
  strArray = Split(OmitMonthsCSV, ",")
  'Adjust for years after the first
  If DateDiff("m", StartDate, dteEnd) > 12 Then
    dteEnd = DateAdd("m", (Year(dteEnd) - Year(StartDate)) * UBound(strArray), dteEnd)
  End If
  'Now adjust for the first 12 months
  For intItem = 0 To UBound(strArray)
    'Start and end in different years
    If Year(StartDate) <> Year(dteEnd) Then
      If CInt(strArray(intItem)) >= Month(StartDate) And CInt(strArray(intItem)) <= 12 Then
        dteEnd = DateAdd("m", 1, dteEnd)
      End If
      If CInt(strArray(intItem)) >= 1 And CInt(strArray(intItem)) <= Month(dteEnd) Then
        dteEnd = DateAdd("m", 1, dteEnd)
      End If
    'Start and end in the same year
    Else
      If (CInt(strArray(intItem)) >= Month(StartDate) And CInt(strArray(intItem)) <= Month(dteEnd)) Then
        dteEnd = DateAdd("m", 1, dteEnd)
      End If
    End If
  Next intItem
End If

'Adjust for weekend days
Select Case Weekday(dteEnd, vbSunday)
  Case 1
    dteEnd = dteEnd + 1
  Case 7
    dteEnd = dteEnd + 2
End Select


'Adjust for Holidays
If HolidaysCSV <> "" Then
  strArray = Split(HolidaysCSV, ",")
  For intItem = 0 To UBound(strArray)
    If dteEnd = CDate(strArray(intItem)) Then
      'Double check Holiday
      Select Case Weekday(dteEnd, vbSunday)
        Case 1, 7
          'Do nothing, it's a weekend day
        Case Else
          dteEnd = dteEnd + 1
      End Select
    End If
  Next intItem
End If

DateAddCustom = dteEnd
End Function

Sub TestIt()
Debug.Print "1 month from 8/25/2006 excluding Dec, Jan, Feb: " & DateAddCustom("m", 1, #8/25/2006#, "12,1,2")
Debug.Print "1 month from 3/1/2006 excluding Dec, Jan, Feb: " & DateAddCustom("m", 1, #3/1/2006#, "12,1,2")
Debug.Print "1 month from 11/1/2006 excluding Dec, Jan, Feb: " & DateAddCustom("m", 1, #11/1/2006#, "12,1,2")
Debug.Print "6 months from 6/1/2006 excluding Dec, Jan, Feb: " & DateAddCustom("m", 6, #6/1/2006#, "12,1,2")
Debug.Print "6 months from 7/1/2006 excluding Dec, Jan, Feb: " & DateAddCustom("m", 6, #7/1/2006#, "12,1,2"), "Off +2 months"
Debug.Print "1 year from 9/1/2006 excluding Dec, Jan, Feb: " & DateAddCustom("yyyy", 1, #9/1/2006#, "12,1,2")
'The above should return 3/3/2008, if 3/3/2008 happened to be a holiday
Debug.Print "1 month from 9/1/2006 excluding Dec, Jan, Feb and 1/1/08, 3/3/08: " & DateAddCustom("yyyy", 1, #9/1/2006#, "12,1,2", "01/01/2008,03/03/2008")

Debug.Print "1 month from 8/25/2006 excluding Jul, Sep, Nov: " & DateAddCustom("m", 1, #8/25/2006#, "7,9,11")
Debug.Print "2 month from 8/25/2006 excluding Jul, Sep, Nov: " & DateAddCustom("m", 2, #8/25/2006#, "7,9,11")
Debug.Print "3 month from 8/25/2006 excluding Jul, Sep, Nov: " & DateAddCustom("m", 3, #8/25/2006#, "7,9,11")
End Sub

Hope this helps,
CMP

[small]P.S. It doesn't seem like it should be this hard. I might be stuck in a rut?[/small]

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)