How to automatically populate a future date

[sabavno]

Hi

I need to automatically populate a date that will be 10 days later after the today's date (date when the record was created)

Actually, the problem is even bigger, because I need to count only business days,

But I am looking for any solutions (even if it will be just a counting of the 10 calendar days)

Thanks a lot.

 

[dpimental]

Here is one solution for adding 10 days to make a future date.

Dim mydate As Date
mydate = Date
mydate = mydate + 10
MsgBox (mydate)

It'll take a little more complexity to get business days
(and holidays too).

David I'm Your Huckleberry!

 

[jebry]

Hi!

Counting ten calendar days is simple just use the DateAdd function DateAdd("d", 10, Date()) will give you ten days from today. Ten business days is more difficult because you can't just use two weeks, ie put a 14 instead of a 10 in the function above, though that would certainly be more accurrate then using a 10. To account for holidays you need to build a table of business holidays and use that to check if any of the days in your time period is a holiday.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[sabavno]

Yeah, I understand that I would have to create a table of the holidays.

But what about Saturday and Sunday, can I handle them without creating a table?

 

[jebry]

Hi!

Saturday and Sundays are covered by starting with a basic +14 in the date add function. You just increment the 14 for every holiday in the range. Therefore, if the range goes over Thanksgiving and you take Thursday and Friday off then you need to use a +16. Then you need to check the day of the week that make you fall on and add two more if it is a Saturday and one more if it is a Sunday.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dpimental]

Check out the link below.

Look at the FAQ linked by j. jones.

thread181-343737 I'm Your Huckleberry!

 

[MichaelRed]

Hmmmmmmmmmmmmm,

some posting troubles here today. I'm SURE I replied earlier - but perhaps the weirdeness got to me. anyway, I said that one who ' ... could figure out the number of days between tow dates SHOULD be able to compute the data n days from some date (and do the holiday . weekend skip thinggy), But if not:

Public Function basSchedDate(DtIn As Date, NDays As Integer) As Date

    'Michael Red 8/24/02;    Tek-Tips thread181-343737
    '?basSchedDate(#8/23/2002#, 30)
    '10/4/02

    'With the usual disclaimers re this being a DEMO.  Error trapping and
    'decisions re the return Values for such are STRICTLY the responsability
    'of the user.

    'In an actual production process, the 'Holidate' array
    'herein should be placed in a seperate RECORDSET, and an administrative
    'Process (e.g. a form for others to use to set/edit the dates) should
    'be provided.
    
    'If the Holidate within the Bounds of the Holidates is not checked,
    '(and Suitable Rtetrn Codes issued) you MAY be obtaining incorrect results.
    'The 'BUG' willdificult to spot, as the dates will still show up as
    '"week days", but the interval could be off by however many "holidays"
    'SHOULD have been in the interval

    Dim Holidate(23) As Date
    Dim CurDate As Date
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim HoliFlag As Boolean
    Dim MyWkDay As Integer

    'HoliDate                     'HoliName
    Holidate(0) = #1/1/2002#      'New Year's Day
    Holidate(1) = #1/17/2002#     'Martin Luther King Day
    Holidate(2) = #2/2/2002#      'Groundhog Day
    Holidate(3) = #2/12/2002#     'Lincon's Birthday
    Holidate(4) = #2/14/2002#     'Valentine's Day
    Holidate(5) = #2/21/2002#     'President's Day
    Holidate(6) = #2/22/2002#     'Washington's Birthday
    Holidate(7) = #3/8/2002#      'Ash Wednesday
    Holidate(8) = #3/17/2002#     'St. Patrick's Day
    Holidate(9) = #4/1/2002#      'April Fool's Day
    Holidate(10) = #4/20/2002#     'Passover
    Holidate(11) = #4/21/2002#     'Good Friday
    Holidate(12) = #5/5/2002#      'Cinco de Mayo
    Holidate(13) = #5/14/2002#     'Mother's Day
    Holidate(14) = #6/11/2002#     'Pentecost
    Holidate(15) = #6/18/2002#     'Father's Day
    Holidate(16) = #7/4/2002#      'Independence Day
    Holidate(17) = #8/21/2002#     'My Birthday
    Holidate(18) = #9/4/2002#      'Labor Day
    Holidate(19) = #10/31/2002#    'Halloween
    Holidate(20) = #11/11/2002#    'Vetran's Day
    Holidate(21) = #11/23/2002#    'Thanksgiving
    Holidate(22) = #12/25/2002#    'Christmas
    Holidate(23) = #12/31/2002#    'New Year's Eve

    Idx = 0
    CurDate = DtIn

    If (CurDate < Holidate(0)) Then
        'Users need to implement an Err Rtn Code here
        Exit Function
    End If

    While Idx < NDays
        MyWkDay = Weekday(CurDate)

        If (MyWkDay = vbSunday Or MyWkDay = vbSaturday) Then

            HoliFlag = True

         Else

            Jdx = 0
            While Jdx <= UBound(Holidate)

                If (Holidate(Jdx) = CurDate) Then
                    'Holiday.  Don't Count
                    HoliFlag = True
                    GoTo HolidayFlg
                End If
                Jdx = Jdx + 1
            Wend
HolidayFlg:
        End If

        If (HoliFlag = False) Then
            Idx = Idx + 1
        End If

        If (CurDate < Holidate(UBound(Holidate))) Then
            'Users need to implement an Err Rtn Code here
            Exit Function
        End If
        CurDate = DateAdd(&quot;d&quot;, 1, CurDate)
        HoliFlag = False

    Wend

    basSchedDate = CurDate

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[raskew]

The following function will add days to a specified date, excluding any days
of the week you specify. While this example excludes Saturdays (7) and
Sundays (1), you could exclude any days desired, e.g., Monday,
Wednesday and Friday ("246&quot

Function AddWkDays(vardate As Variant, numdays As Integer, _
pexclude As String)
'*******************************************
'Name:      AddWkDays (Function)
'Purpose:   Simple, non-formula method of
'                 adding weekdays to a given
'           	    date
'Inputs:    ? AddWkdays("08/04/94", 10, "17")
'Output:    8/18/1994
'Note:      pexclude = the weekdays to exclude
'           (e.g., Sat = 7, Sunday = 1)
'*******************************************
Dim thedate As Date, n As Integer, incl As Boolean

thedate = DateValue(vardate)
incl = False
Do While InStr(pexclude, WeekDay((thedate) + 1)) > 0
  thedate = thedate + 1
  incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
   If InStr(pexclude, WeekDay(thedate)) = 0 Then
      n = n + 1
   End If
   If n = numdays Then Exit Do
   thedate = thedate + 1
Loop
AddWkDays = thedate
End Function

The above code is easily modified to also consider holidays.

Function AddWkDaysH(vardate As Variant, numdays As Integer, _
pexclude As String, holidates As String)
'*******************************************
'Name:      AddWkDays (Function)
'Purpose:   Simple, non-formula method of
'           adding weekdays to a given
'           date
'Inputs:    ? ? AddWkdaysH("08/25/02", 10, "17", "37501")
'Output:    9/9/02
'Note:      pexclude = the weekdays to exclude
'              (e.g., Sat = 7, Sunday = 1)
'	 the holiday string uses the date as stored by 
'	Access, e.g. US Labor Day, first Monday in September,
'	is 9/2/02.  Access stores this as cDbl(#9/2/02) = 37501
'*******************************************
Dim thedate As Date, n As Integer, incl As Boolean

thedate = DateValue(vardate)
incl = False
Do While InStr(pexclude, WeekDay((thedate) + 1)) > 0 Or InStr(holidates, CLng((thedate) + 1)) > 0
  thedate = thedate + 1
  incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
   If InStr(pexclude, WeekDay(thedate)) = 0 And InStr(holidates, CLng(thedate)) = 0 Then
      n = n + 1
   End If
   If n = numdays Then Exit Do
   thedate = thedate + 1
Loop
AddWkDaysH = thedate
End Function
[code]

How you develop your table of holidates is a whole separate topic.  If, however, you have an
accurate table of holidates, a calculated field in a query, using a variation of [b] AcDate: CDbl([theDate])[/b] will return  the date as stored by Access.  The reason for using this, rather
than short-date, is space and ease of coding.

Couple of other considerations:
(1) You'll probably wanted a 'Selected' (check-box) field to allow the user to tailor the holidays to be considered (e.g., St. Patrick's Day in most locales is a business day).
(2) What happens when your calculation spans one or more years?  You cannot just take the month/day of the previous year and add the new year  (e.g., the previous input used what is represented as a calendar for 2002, but it in fact appears to be for 2000.  Only the dates that fall on a specific calendar date (Christmas, New Years, etc.) are correct.