Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

How to look up date ahead in VBA

I have a date variable. I need to be able to determine a date of 30 business days from today or that variable. Example 05/19/09, I need to find away to obtain 06/30/09,which is 30 business days from today. Also if there is anyway to exclude the holidays in the future date look up.
0
RalphyC
Asked:
RalphyC
  • 2
  • 2
5 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
At it's simplest, you use DateAdd() to add or subtract days from a date (you can also just do +30 and it will work).
But you said "business days", which typically means Mon - Friday.  That's not a straight calculation, but requires some code.  You also wanted to include holidays.  That means adding a table lookup to that and taking the holidays into account.
This has been covered many times before on EE and if you do a quick search, you can easily come up with the code.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
0
 
Rey Obrero (Capricorn1)Commented:
you will need a function to do that,
place this codes in a regular module

Function fgetDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7
        dtStart = dtStart + 1
        i = i + 1
    Loop
    dtStart = dtStart + 1
Next
fgetDueDate = DateAdd("d", (Span + i), dDate)

End Function

to use

fgetDueDate(#5/19/09#,30)
0
 
Rey Obrero (Capricorn1)Commented:
if you want to exclude holidays, you will need a table for holidays date
tblHolidays with field observedDate

Function fgetDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("observedDate", "tblHolidays", "observedDate=#" _
     & dtStart & "#"))
        dtStart = dtStart + 1
        i = i + 1
    Loop
    dtStart = dtStart + 1
Next
fgetDueDate = DateAdd("d", (Span + i), dDate)

End Function
0
 
Patrick MatthewsCommented:
Hello RalphyC,

Here is another alternative, which allows you to specify at runtime which weekdays are "business days",
go forward or backward in determining the business day offset, and use a holiday table to override the
normal settings.

Example:

SELECT StartDate, GetBusinessDay(StartDate, 30, "23456", 1, "tblHolidays", "HolidayDt") AS 30BusDays
FROM SomeTable

To go backward 30 business days...

SELECT StartDate, GetBusinessDay(StartDate, -30, "23456", 1, "tblHolidays", "HolidayDt") AS 30BusDays
FROM SomeTable

To include Saturday as a business day...

SELECT StartDate, GetBusinessDay(StartDate, 30, "234567", 1, "tblHolidays", "HolidayDt") AS 30BusDays
FROM SomeTable

etc.

Regards,

Patrick
Function GetBusinessDay(FromDate As Date, Offset As Long, Optional WorkDays As String = "23456", _
    Optional ZeroOffsetBehavior As Long = 1, Optional HolidayTblName As String = "", _
    Optional HolidayDateField As String = "")
    
    ' Working from a starting date, this function returns an offset "business day" from that start date.
    ' The function allows user to specify which weekdays are counted as "work days", and to specify a
    ' table in which holidays are stored -- holidays override normal work days
    '
    ' This function requires a reference to a DAO library!!!
    '
    ' This function is meant for Access, and is based loosely on Excel's WORKDAY() function.  The key
    ' differences are that WORKDAY() does not allow you to specify the "work days", and WORKDAY() always
    ' returns the start date itself if the offset is zero
    '
    ' FromDate:             The starting point from which to calculate the business day
    '
    ' Offset:               The number of days you want to "move".  If negative, it gets a business day
    '                       prior to FromDate.  If positive, it moves forward in time.  If zero, it tests
    '                       FromDate itself, and the return value depends on the ZeroOffsetBehavior argument
    '
    ' Workdays:             String containing the weekday indices for the weekdays considered to be regular
    '                       "work days".  1 = Sun, 2 = Mon, 3 = Tue, ..., 7 = Sat.  Default is Mon-Fri
    '
    ' ZeroOffsetBehavior:   For Offset = 0, if FromDate is a work day and is not a holiday, return FromDate.
    '                       If FromDate is either not a work day or is a holiday, then...
    '                       ZeroOffsetBehavior < 0 -- return last previous valid business day
    '                       ZeroOffsetBehavior > 0 -- return next valid business day
    '                       ZeroOffsetBehavior = 0 -- return FromDate even though it is not a business day
    '                       Dafault is 1, which makes the function move forward if necessary
    '
    ' HolidayTblName:       If used, name of table that stores holidays, which override normal work days
    '
    ' HolidayDateField:     If used, name of the column in "holiday table" that has holiday dates
    
    Dim DaysCounter As Long
    Dim TestDate As Date
    Dim Holidays As String
    Dim rs As DAO.Recordset
    
    ' Strip time value from FromDate
    
    FromDate = DateValue(FromDate)
    
    ' If applicable, build up a string that contains the formatted dates of holidays
    
    If HolidayTblName <> "" Then
        ' Test table/column names for bracketing
        If Left(HolidayTblName, 1) <> "[" Then HolidayTblName = "[" & HolidayTblName & "]"
        If Left(HolidayDateField, 1) <> "[" Then HolidayDateField = "[" & HolidayDateField & "]"
        Set rs = CurrentDb.OpenRecordset("SELECT " & HolidayDateField & " FROM " & HolidayTblName)
        Do Until rs.EOF
            Holidays = Holidays & Format(rs.Fields(0).Value, "|yyyy-mm-dd|")
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    End If
    
    ' Logic branches depending on Offset value
    
    Select Case Offset
        
        ' For Offset of zero, GetBusinessDate = FromDate if FromDate is a valid business day; otherwise,
        ' return value depends on ZeroOffsetBehavior
        
        Case 0
            If InStr(1, WorkDays, Weekday(FromDate)) > 0 And _
                InStr(1, Holidays, Format(FromDate, "|yyyy-mm-dd|")) = 0 Then
                
                ' FromDate is a valid business day!
                
                GetBusinessDay = FromDate
                
            ElseIf ZeroOffsetBehavior = 0 Then
                
                ' For this setting, FromDate is returned regardless of whether it is a valid business day
                
                GetBusinessDay = FromDate
                
            ElseIf ZeroOffsetBehavior < 0 Then
                
                ' For this setting, move backward until you hit a valid business day
                
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", -1, TestDate)
                Loop
                GetBusinessDay = TestDate
                
            Else
                
                ' For this setting, move forward until you hit a valid business day
                
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", 1, TestDate)
                Loop
                GetBusinessDay = TestDate
                
            End If
        
        ' For this setting, move forward until you increment the correct number of valid business days
        ' Only increment DaysCounter if TestDate is a valid business day
        
        Case Is > 0
                        
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", 1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter + 1
                End If
            Loop
            GetBusinessDay = TestDate
            
        ' For this setting, move backward until you decrement the correct number of valid business days
        ' Only decrement DaysCounter if TestDate is a valid business day
        
        Case Else
                        
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", -1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter - 1
                End If
            Loop
            GetBusinessDay = TestDate
            
    End Select
        
End Function

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now