Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to look up date ahead in VBA

Posted on 2009-05-19
Medium Priority
239 Views
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
Question by:RalphyC
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 58

Assisted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 640 total points
ID: 24422653
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 58

Assisted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 640 total points
ID: 24422670
0

LVL 120

Assisted Solution

Rey Obrero (Capricorn1) earned 720 total points
ID: 24422725
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 120

Assisted Solution

Rey Obrero (Capricorn1) earned 720 total points
ID: 24422774
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 93

Accepted Solution

Patrick Matthews earned 640 total points
ID: 24423044
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!

ElseIf ZeroOffsetBehavior = 0 Then

' For this setting, FromDate is returned regardless of whether it is a valid business day

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
Loop

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
Loop

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
If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
DaysCounter = DaysCounter + 1
End If
Loop

' 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
If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
DaysCounter = DaysCounter - 1
End If
Loop

End Select

End Function
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll