Calculate calendar days from given date less a set dates

[billieclap]

Hello, re Excel 97 or if possible Access 2000
I have been trying to calculate the date 40 CALENDAR days after a given date, and then go to the next Fridays date. This is complicated by a range of dates that can not be included in the count(a named range of dates or a table, Table1, field1 which has the excluded dates listed)and unfortunately the dates in the named range are not always weekends. "Networkdays" is similar but it does not calculate calendar days. Any assistance will be greatly appreciated, THANK YOU in advance Bill

 

[tripharn]

Hi, I'm very new to Access but I know of a function called DateAdd, using this you could add 40 days to it. It works on the calander days so it will give you the proper date.
Then I think that day(number) that will give you the day of the week and you could find the difference in days to get the actual date.

Other functions that you might want are....
Date() - gives you todays date
DateDiff() - The difference between the dates.

Anyway, I hope that this helps.

Tripharn

 

[tlbroadbent]

You can create a function to determine the nextFriday, after adding 40 days.

Function AddDaysAndFindFriday(NumDays As Integer, InDate As Date) As Date
Dim FriAdj As Integer, OutDate As Date
OutDate = DateAdd("d", NumDays, InDate)
FriAdj = 7 - Weekday(OutDate, vbSaturday)
AddDaysAndFindFriday = DateAdd("d", FriAdj, OutDate)
End Function

Or you can do everything in one line.

DateAdd("d", 7 - Weekday(weekday(dateadd("d",40,date())), vbSaturday), dateadd("d",40,date())) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[raskew]

This query, based on tblDateTry with field StartDate, would do the 40 days/next Friday business. Maybe this will provide a starting point:

SELECT tbldatetry.startDate, DateAdd("d",40,[startdate]) AS x, IIf(Weekday([x])=6,7,Abs((13-Weekday([x])) Mod 7)) AS y, DateAdd("d",[y],[x]) AS z
FROM tbldatetry;

 

[MichaelRed]

I'm sure this is NOT perfect, however it does directly address the inquiry. This has not been thoroughly tested, so one should do some (more) verification than I care to do, and (obviously) the hloidays listed are of my own choosing, so they should be modified accordiing to the USER's needs. I would normally place the 'holidates' in a table, as done in faq181-291, however this technique does not present itself well in these forums, and billieclap specifically opened the door to the use of other mechanisims. the array embeded in the function is -perhaps-the poorest choice of the listing of hte dates, however it does have the advantage of encapsulating the entire functionallity in a single procedure. One would hope/expect users would exercise the good judgement to modify the procedure to move the holidate(s) to a more user friendly environment.

Public Function basFortyDaysAndFriday(StartDate As Date, _
                                      Optional NumDays As Integer = 40) As Date

    'Get the number of workdays between the given dates

    'Usage:    ? basFortyDaysAndFriday(#9/7/01#)
    'Returns: 10/19/01

    'Usage:   ? basFortyDaysAndFriday(#9/7/01#, 30)
    'Returns: 10/12/01


    Dim blnHoliFnd As Boolean
    Dim Holidate(21) As Date
    Dim Idx As Long
    Dim Jdx As Integer
    Dim MyDate As Date

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

    MyDate = Format(StartDate, "Short Date")

    Do While Idx < NumDays
        blnHoliFnd = False
        For Jdx = 0 To UBound(Holidate)
            If (Holidate(Jdx) = MyDate) Then
                blnHoliFnd = True
                Exit For
'             Else
'                Do Nothing, it is NOT a Workday
            End If

        Next Jdx

        If (blnHoliFnd = False) Then
            Idx = Idx + 1
        End If
    
        MyDate = DateAdd(&quot;d&quot;, 1, MyDate)
        

    Loop

    'Got the number of days required.  Now, go to the following Friday
    MyDate = DateAdd(&quot;d&quot;, vbFriday - Weekday(MyDate), MyDate)

    basFortyDaysAndFriday = MyDate

End Function

MichaelRed
m.red@att.net

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