Calculating a date based on today's date (1 Viewer)

[Johnny Drama]

Hi all,

I have a date field in a report that I would like to populate with the date which is 5 business days from the current date (date report is generated + 5 business days). Anyone have any idea how I would do this?

Thanks

 

[Alansidman]

Look here

http://www.access-programmers.co.uk/forums/showthread.php?t=176137

 

[Pat Hartman]

I don't have a function that does that but you will need a table of holidays to do it accurately. The following is a function I copied that calculates the business days between two dates. It may give you an idea of how to do the calculation you need.

Function BusinessDays(startdate As Date, enddate As Date) As Integer

'By Susan Sales Harkins and Doris Manning
'Inside Microsoft Access Feb 2004
' http://download.elementkjournals.com/access/200402/busdays.zip

Dim intHolidays As Integer
Dim intTotalDays As Integer
Dim intWeekendDays As Integer
'Dim rst As New ADODB.Recordset
Dim rst As DAO.Recordset
Dim strSQL As String
Select Case DatePart("w", startdate, vbMonday)  'Week starts on monday
    Case 6
        startdate = DateAdd("d", startdate, 2)  'Saturday
    Case 7
        startdate = DateAdd("d", startdate, 1)  'Sunday
End Select
Select Case DatePart("w", enddate, vbMonday)    'Week starts on monday
    Case 6
        enddate = DateAdd("d", enddate, -1)     'Saturday
    Case 7
        enddate = DateAdd("d", enddate, -2)     'Sunday
End Select
strSQL = "Select Count(*) as HolidayCount From tblHolidays " & _
        "Where HolidayDate BETWEEN #" & startdate & "#" & _
        " AND " & "#" & enddate & "#;"
'rst.Open strSQL, CurrentProject.Connection
Set rst = CurrentDb.OpenRecordset(strSQL)
intHolidays = rst!HolidayCount                                      'Count Holidays between dates
intTotalDays = DateDiff("d", startdate, enddate) + 1                'Calc dif in days
intWeekendDays = DateDiff("ww", startdate, enddate, vbMonday) * 2   'Calc dif in weeks and multiply by 2
BusinessDays = intTotalDays - intWeekendDays - intHolidays
Set rst = Nothing

End Function

HolidayDate	HolidayDescription
12/31/2010	New Year's Day
01/03/2011	New Year's Day-day after
01/17/2011	Martin Luther King Day
02/21/2011	Presidents' Day
05/31/2011	Memorial Day
07/04/2011	Independence Day
09/05/2011	Labor Day
11/24/2011	Thanksgiving
11/25/2011	Thanksgiving-day after
12/26/2011	Christmas

 

[Johnny Drama]

Thanks...those examples should do the trick!