?
Solved

To know 31st working date from the date entered

Posted on 2010-01-04
7
Medium Priority
?
281 Views
Last Modified: 2012-05-08
Hi,

Would like to know 31st working date from the entered date
0
Comment
Question by:pg1533
6 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 26173109
Dateadd function

SELECT
DateAdd('d',31,Table1.Datefield) AS Date31
FROM Table1;
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26173118
Hello pg1533,

The following two functions, if added to a regular VBA module in your project, may be helpful:




Public Function WorkdayX(StartDate As Date, DayOffset As Long, UseDays As String, ZeroMode As Long, _
    ParamArray Holidays()) As Date
   
    ' Function returns a date offset from the StartDate argument the number of days determined by
    ' the DayOffset argument, counting only "work days" specified by the UseDays argument.  Function
    ' works in Excel, VBA, or VB6 projects, although for Access it may work better to use a function
    ' that grabs holidays from a table
   
    ' Function differs from the Excel function WORKDAY in that WORKDAY always counts Monday-Friday,
    ' while WorkdayX allows the user to specify the days that should be counted.  Also, with WORKDAY,
    ' if the offset is zero, the function always returns the start date as the answer, even if the
    ' start date is on a weekend.  WorkdayX allows the user to specify what should happen if the offset
    ' is zero and the start date is on a non-working day.
   
    ' Arguments:
    ' 1) StartDate is the original starting point, from which you want a working day offset
    ' 2) DayOffset is the number of whole days away from the starting point, counting only "working days".
    '    A zero offset is allowed
    ' 3) UseDays is a string encoding what weekdays count as working days.  Use 1=Sun, 2=Mon, ..., 7=Sat.
    '    Thus, "23456" indicates Mon-Fri should be counted, and "147" means only Sun, Wed, and Sat count.
    '    Use holidays to override this working day setting for particular dates
    ' 4) ZeroMode controls how the function behaves when the offset is zero and the start date is a non
    '    working day.  Use 0 when you want the function to return the start date (as Excel's WORKDAY
    '    function would), 1 to pick the next working day, or -1 to pick the previous working day
    ' 5) Holidays is an optional array of holidays that never count as working days, even if they fall on
    '    a weekday that would normally count as per the UseDays argument
   
    Dim TestDate As Date                ' a date to be tested for being a working day or not
    Dim Moved As Long                   ' cumulative number of working days moved so far
    Dim x As Variant, y As Variant      ' used for looping through Holidays
    Dim DateStr
    Static HolidayDic As Object         ' stores holidays
   
    ' Build a Dictionary with the holidays in it.  The variable is Static, so it preserves its state in
    ' between calls (and thus this gets skipped if the Dictionary already exists
   
    If HolidayDic Is Nothing Then
        Set HolidayDic = CreateObject("Scripting.Dictionary")
        If Not IsMissing(Holidays) Then
            For Each x In Holidays
                For Each y In x
                    If Not HolidayDic.Exists(Format(y, "d mmm yyyy")) Then
                        HolidayDic.Add Format(y, "d mmm yyyy"), y
                    End If
                Next
            Next
        End If
    End If
   
    ' Initialize the TestDate
   
    TestDate = StartDate
   
    ' If the offset is 0, we have special handling to determine how the function behaves.  The special handling
    ' only applies if the start date is not a working day
   
    DateStr = Format(StartDate, "d mmm yyyy")
    If DayOffset = 0 And (InStr(1, UseDays, Weekday(StartDate)) = 0 Or HolidayDic.Exists(DateStr)) Then
        If ZeroMode > 0 Then
            DayOffset = 1
        ElseIf ZeroMode < 0 Then
            DayOffset = -1
        Else
            WorkdayX = StartDate
            Exit Function
        End If
    ElseIf DayOffset = 0 Then
        WorkdayX = StartDate
        Exit Function
    End If
   
    ' Starting with the start date, move forward or backward one day at a time from the start date, the
    ' direction determined by the sign of the DayOffset argument.  Each one-day movement only "counts"
    ' if the new date is a working day.  A working day is day for which its weekday index appears in
    ' the UseDays string, and its formatted date string does not appear in the HolidayStr variable
   
    If DayOffset < 0 Then
        Do Until Moved = Abs(DayOffset)
            TestDate = TestDate - 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
        Loop
    Else
        Do Until Moved = DayOffset
            TestDate = TestDate + 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
        Loop
    End If
   
    WorkdayX = TestDate
   
End Function

Public Function WorkdayXHolidayTbl(StartDate As Date, DayOffset As Long, Optional UseDays As String = "23456", _
    Optional ZeroMode As Long = 1, Optional HolidayTbl As String = "", Optional HolidayCol As String = "") As Date
   
    ' This function requires a reference to the DAO library
   
    ' Function returns a date offset from the StartDate argument the number of days determined by
    ' the DayOffset argument, counting only "work days" specified by the UseDays argument.  Function
    ' is designed for Access, as it assumes that holidays are stored in a holiday table
   
    ' Function differs from the Excel function WORKDAY in that WORKDAY always counts Monday-Friday,
    ' while WorkdayXHolidayTbl allows the user to specify the days that should be counted.  Also, with
    ' WORKDAY, if the offset is zero, the function always returns the start date as the answer, even if
    ' the start date is on a weekend.  WorkdayXHolidayTbl allows the user to specify what should happen
    ' if the offset is zero and the start date is on a non-working day.
   
    ' Arguments:
    ' 1) StartDate is the original starting point, from which you want a working day offset
    ' 2) DayOffset is the number of whole days away from the starting point, counting only "working days".
    '    A zero or negative offset is allowed
    ' 3) UseDays is a string encoding what weekdays count as working days.  Use 1=Sun, 2=Mon, ..., 7=Sat.
    '    Thus, "23456" indicates Mon-Fri should be counted, and "147" means only Sun, Wed, and Sat count.
    '    Use holidays to override this working day setting for particular dates
    ' 4) ZeroMode controls how the function behaves when the offset is zero and the start date is a non
    '    working day.  Use 0 when you want the function to return the start date (as Excel's WORKDAY
    '    function would), 1 to pick the next working day, or -1 to pick the previous working day
    ' 5) HolidayTbl is an optional argument that is the name of the table that stores holidays, which
    '    never count as working days, even if they fall on a weekday that would normally count as per the
    '    UseDays argument
    ' 6) HolidayCol is the name of the column in the "holiday table" that contains the holiday dates
   
    Dim TestDate As Date                ' a date to be tested for being a working day or not
    Dim Moved As Long                   ' cumulative number of working days moved so far
    Dim x As Variant, y As Variant      ' used for looping through Holidays
    Dim DateStr                         ' used to construct key for Dictionary items
    Dim rs As DAO.Recordset
    Static HolidayDic As Object         ' stores holidays
    Static LastCall As Date             ' datetime for last function call
   
    ' As a Static variable, HolidayDic will persist in memory, which can be dangerous if there have
    ' been database updates since the last run.  Thus, test to see how long it's been since the last
    ' run.  If it's been 30 seconds or more, then force a rebuild of the dictionary to make sure we
    ' incorporate the most recent data available
   
    If DateDiff("s", LastCall, Now) >= 30 Then Set HolidayDic = Nothing
   
    LastCall = Now
   
    ' Build a Dictionary with the holidays in it.  The variable is Static, so it preserves its state in
    ' between calls (and thus this gets skipped if the Dictionary already exists
   
    If HolidayDic Is Nothing Then
        Set HolidayDic = CreateObject("Scripting.Dictionary")
       
        ' Skip populating the Dictionary if the table and/or column names are omitted
       
        If HolidayTbl <> "" And HolidayCol <> "" Then
           
            ' Make sure db object names are bracketed
           
            If Left(HolidayTbl, 1) <> "[" Then HolidayTbl = "[" & HolidayTbl
            If Right(HolidayTbl, 1) <> "]" Then HolidayTbl = HolidayTbl & "]"
            If Left(HolidayCol, 1) <> "[" Then HolidayCol = "[" & HolidayCol
            If Right(HolidayCol, 1) <> "]" Then HolidayCol = HolidayCol & "]"
           
            ' If the database objects do not exist, which would throw an error, then skip populating
            ' the Dictionary
           
            On Error Resume Next
            Set rs = CurrentDb.OpenRecordset("SELECT " & HolidayCol & " FROM " & HolidayTbl)
            If Err = 0 Then
           
                ' Loop through recordset and add Dictionary items as needed
               
                Do Until rs.EOF
                    DateStr = Format(rs.Fields(0).Value, "d mmm yyyy")
                    If Not HolidayDic.Exists(DateStr) Then HolidayDic.Add DateStr, rs.Fields(0).Value
                    rs.MoveNext
                Loop
                rs.Close
            Else
                Err.Clear
            End If
            Set rs = Nothing
            On Error GoTo 0
        End If
    End If
   
    ' Initialize the TestDate
   
    TestDate = StartDate
   
    ' If the offset is 0, we have special handling to determine how the function behaves.  The special handling
    ' only applies if the start date is not a working day
   
    DateStr = Format(StartDate, "d mmm yyyy")
    If DayOffset = 0 And (InStr(1, UseDays, Weekday(StartDate)) = 0 Or HolidayDic.Exists(DateStr)) Then
        If ZeroMode > 0 Then
            DayOffset = 1
        ElseIf ZeroMode < 0 Then
            DayOffset = -1
        Else
            WorkdayXHolidayTbl = StartDate
            Exit Function
        End If
    ElseIf DayOffset = 0 Then
        WorkdayXHolidayTbl = StartDate
        Exit Function
    End If
   
    ' Starting with the start date, move forward or backward one day at a time from the start date, the
    ' direction determined by the sign of the DayOffset argument.  Each one-day movement only "counts"
    ' if the new date is a working day.  A working day is day for which its weekday index appears in
    ' the UseDays string, and its formatted date string does not appear in the HolidayStr variable
   
    If DayOffset < 0 Then
        Do Until Moved = Abs(DayOffset)
            TestDate = TestDate - 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
        Loop
    Else
        Do Until Moved = DayOffset
            TestDate = TestDate + 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
        Loop
    End If
   
    WorkdayXHolidayTbl = TestDate
   
End Function



To give the right SQL or VBA expression for you...

1) Is this being used in a query, or in a VBA expression?

2) What count as "workdays"?  The usual Mon-Fri, or something else?

3) What about holidays?

Assuming that you store your holidays in a table tblHolidays (date column HolidayDt), and that you use Mon-Fri
as workdays, here is an example:

SELECT OriginalDate, WorkdayXHolidayTbl(OriginalDate, 31, "23456", 1, "tblHolidays", "HolidayDt") AS 31stWorkDay
FROM SomeTable




Regards,

Patrick
0
 
LVL 31

Expert Comment

by:hnasr
ID: 26173442
In access help, search for workday.

"Access Developer Reference
How to: Find the Number of Working Days Between Two Dates

Microsoft Access does not have a built-in function to determine the number of working days between two dates. The following user-defined function illustrates how to calculate the number of working days between two dates.

   Note
This function does not account for holidays.

Function
 Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
......."
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26203859
pg1533,

You do realize that the selected comment does not answer the question, right?

Patrick
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 26277705
Here's the repost:
Our commercial source code library, Total Visual SourceBook, has lots of code related to date math including real business day math where a list of holidays are kept so you don't count those dates.
I realize a commercial solution isn't viable for everyone but an example of some of the royalty-free source code in Total Visual SoruceBook is posted online. It happens that one of this is weekday math:
http://www.fmsinc.com/Products/sourcebook/SampleCode.htm#Function_to_Add_Weekdays_to_a_Date 
Hope this helps.
0
 
LVL 3

Accepted Solution

by:
Zlatko Kuzmanovski earned 500 total points
ID: 26293186
Hi, (a copy from the other post!!!)

I've worked on your Renewal.mdb database, and updated it. The MAIN table has new fields (to save the test data), also the MAIN form has been updated ..
Please test the attached and updated Renewal.mdb, and let me know...
Thank you,

Regards,
Zlatko.
Renewal.mdb
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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