Solved

How to look up date ahead in VBA

Posted on 2009-05-19
5
227 Views
Last Modified: 2013-11-27
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
Comment
Question by:RalphyC
  • 2
  • 2
5 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 180 total points
Comment Utility
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 180 total points
Comment Utility
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 160 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now