Calculate workdays (and defined holidays) between two dates (2 Viewers)

[ECEK]

Christ have you guys REALLY got NO friends ?

 

[ECEK]

So using the word "apologise" in the same sentence as berating the people you are apologising to?

That's called being disingenuous. Or ironic and us Brits call it.

It's where I say "I'm sorry" but I don't really mean it. And yet someone on a web site STILL thinks I'm incapable of communicating correctly.

Read the posts again. See how the sanctimonious answers fueled the fire.

Lest we beg forgiveness to be treated to the truth.

 

[ECEK]

Anyway you'll sleep well knowing that I haven't been able to work any of this out.
I bet you kick cripples across the road and then expect a thank you?

 

[jdraw]

Further to what the others are saying, let me assure you that having been in database and information management area since ~1977, you will need thicker skin than you have exhibited on some of these posts. Also, you can learn something from everyone if you listen long enough.

Apologies are best delivered with sincerity as stopher said. It is better to seek forgiveness and understanding by clearly stating your situation and approach positively.

So much for the soap box. If you read through various forums and posts, you'll see that may posters "assume" that readers understand their issues with almost perfect clairvoyance. When the reality is we know little of the poster; less of their environment and abilities; and even less of the "jargon-ese" used to describe the issue/opportunity; and spend countless posts just to understand the basic "what" of their "distress". So, forum regulars are familiar with the "teeth-pulling" exercises to get to the heart of most issues. When people here (or other forums) ask for clarity and examples, and even sample data, it isn't because we're "smart asses", it's because a big picture view is helpful to put issues/posts/mis-guided concepts and approaches into context. Often, we have to back track to the original requirements to get some appreciation of the issue/opportunity.

With over 300 posts, this should not be new to you. And I am not suggesting it is. What I am advocating is some understanding that we are trying to help, and some appreciation of the volunteering of information, experience and expertise that members provide.

Because you are under pressure at work is not a rational reason for

You were stupid once? Hey..for all I know you still are. Your'e Ill mannered I know that.

Yanks don't do irony ! Ask one of your English friends (if you have any that can stand you)

Name calling won't work here, but we do recognize your use of terms like "belittle" as a sign of stress and frustration on your part. That you haven't learned as much vba/database as you would like, or need for your current job, is not something we can resolve. That is an observation you have made and should take up with your management.

A friendlier attitude in any endeavor is always a good start.

Also: For vba learning I recommend the 70+ free video series by Steve Bishop which is listed here.

Good luck with your project.

 

[ECEK]

Thanks for the link. I shall never again ask a question until I know how to do what it is that I need to know the answer to.

 

[jdraw]

That is a poor strategy that may change because of your expressed pressure at work.

Also, be cautious of functions you find online that do not have test routines. Always check with your own data.


Good luck.

 

[jdraw]

Anyway you'll sleep well knowing that I haven't been able to work any of this out.
I bet you kick cripples across the road and then expect a thank you?

I gave you a link to a function. You can use it/copy it. I've shown you the results of samples you asked for....
What is it you aren't able to work out???

 

[ECEK]

I have no idea what a test routine is ?

That's why I posted my question.

I'm still drowning, I'm clutching at straws !!

 

[jdraw]

The function that I wrote, tested and posted here can be copied into your database.

And it can be called upon to give you the number of work days/ business days between 2 dates and will use a holiday table.

A test routine (in this case) is nothing more than a small program with some test data that calls a function. The test is to ensure it produces the result it claims.

What part of this do you not understand?

 

[stopher]

I have no idea what a test routine is ?

That's why I posted my question.

I'm still drowning, I'm clutching at straws !!

Take a look at the attached.

Open the VBA window and you will see a module (module1). It has a test routine at thee top and the actual function further down. The test routine calls the function using some chosen parameters. The output is displayed in the "Immediate" window. To run the test procedure, just press F5 and select the test procedure name.

So you can try lots of different values by changing the values in the test procedure.

Please note I have note tested myself. I just did what JDraw said.

Note comments about "between" you may need to adjust the code slightly. Or you can maybe call the function like this: fWorkingDays(startdate+1, enddate-1)

hth
Chris

 

[ECEK]

Stopher: many thanks for your patience and direction. I have looked at your vba code in the database that you attached. It's really useful to slowly begin to understand. The results of the code(however)are not correct. The result of your code is 20. I also tried this:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

and called it doing this:

Sub testECEKWD()
Dim StartDate As Date: StartDate = #12/1/2016#  'm/d/y US
Dim EndDate As Date: EndDate = #1/4/2017#
Debug.Print WorkingDays2(StartDate, EndDate)
End Sub

But still I get 20. jdraw gets 19 (which is the correct answer!)

I can't seem to fatham it at all.

 

[stopher]

Did you check the holiday table. I just copied the list but it's obviously not uk hols.

 

[ECEK]

Well after all the Hoo Har I found the solution. Here you go.

Public Function fNetWorkdays(ByVal dtStartDate As Date, ByVal dtEndDate As Date, _
                             Optional blIncludeStartdate As Boolean = False) _
                             As Long
'Returns the number of workdays between the two passed dates.  Saturdays and
'Sundays are NOT considered workdays.  Plus there is an assumption that a
'table exists that is named tblHolidays that identifies EACH holiday date
'in a field named HolidayDate.  By default the function will NOT count the
'first date in the range as a work date, if you pass a True value to
'blIncludeStartdate, the function will count the start date as a work date
'if it is not a Saturday,Sunday or Holiday.
'''''''''''''''''''''''''''''''''''''''''''
'Author: Brent Spaulding
'Version: 8
'Date: Jun 7 2011
'''''''''''''''''''''''''''''''''''''''''''
'Ver    Description
'?-3    Intial releases to UA in various threads and the Code Archive
'4      Made the function cabable of handling Start dates that are Greater
'       than End dates
'5      Fixed bug when the start date was a holiday and the SQL when end < start
'6      Modified the structure a bit, logically equivalent, but I only test
'       for dtStartDate <= dtEndDate once, instead of 3 times.
'7      Formated date literals to corrected for possible errors with
'       NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
'8      Fixed but when start date is Weekend or Holiday and blIncludeStartdate was false.
'..........................................
    
    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngSundays As Long
    Dim lngHolidays As Long
    Dim lngAdjustment As Long
    Dim blStartIsHoliday As Boolean
    Dim strSQL As String
    
    'Count the number of RAW days between the dates ...
    lngDays = Abs(DateDiff("d", dtStartDate, dtEndDate))
    
    'Count the number of Saturdays & Sundays between the two dates.  Note the use of "w" as
    'the date interval which will count the <day of first date in DateDiff()>.
    'So, to count the Saturdays, I adjust the start date of the datediff function
    'to the saturday BEFORE the dtStartDate of the passed range, thus the number
    'of Saturdays between the passed range is returned.  Investigated "ww"
    'for Sundays, but when the end is less than the start, problems arose.
    'This block also builds the SQL for extracting holidays.
    If dtStartDate <= dtEndDate Then
    
        lngSaturdays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
                                dtStartDate, _
                                dtStartDate - Weekday(dtStartDate, vbSunday)), _
                                dtEndDate))
    
        lngSundays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSunday, _
                                dtStartDate, _
                                dtStartDate - Weekday(dtStartDate, vbSunday) + 1), _
                                dtEndDate))
    
        strSQL = "SELECT HolidayDate FROM tblHolidays" & _
                 " WHERE HolidayDate" & _
                        " Between #" & Format(dtStartDate, "yyyy-mm-dd") & "#" & _
                            " And #" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
                        " And Weekday(HolidayDate, 1) Not In (1,7)" & _
                 " ORDER BY HolidayDate DESC"
    
    Else
    
        lngSaturdays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
                            dtStartDate, _
                            dtStartDate + (7 - Weekday(dtStartDate, vbSunday))), _
                            dtEndDate))
    
        lngSundays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSunday, _
                            dtStartDate, _
                            dtStartDate + (7 - Weekday(dtStartDate, vbSunday)) + 1), _
                            dtEndDate))
    
        strSQL = "SELECT HolidayDate FROM tblHolidays" & _
                 " WHERE HolidayDate" & _
                        " Between #" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
                            " And #" & Format(dtStartDate, "yyyy-mm-dd") & "#" & _
                        " And Weekday(HolidayDate, 1) Not In (1,7)" & _
                 " ORDER BY HolidayDate DESC"
    
    End If
    
    'Count the number of holidays AND determine if the start date is a holiday
    'the SQL is built in the IF..Then above.
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not .EOF Then
    
            'Get the number of holidays between the dates specified.
            'Need to populate a DAO recordset to ensure a good rcd count
            .MoveLast
    
            'Determine if the start date is a holiday.  Since the rst is
            'in descending order the last record SHOULD be no earlier than
            'the start date, so if the start date is equal to the LAST record
            'then, the start date is a holiday.... Unless we are in a "Negative"
            'situation, then the FIRST record must be checked.
            If dtStartDate > dtEndDate Then
                .MoveFirst
            End If
    
            'Determine if the start is a holiday ... if it is, then DON'T include
            'it in the count of holidays since the first day is NOT included by
            'default in the total network days...
            blStartIsHoliday = (!HolidayDate = dtStartDate)
            If blStartIsHoliday Then
                lngHolidays = .RecordCount - 1
            Else
                lngHolidays = .RecordCount
            End If
    
        End If

        .Close

    End With
    
    'Make an adjustment based different situations ... basically if the start is
    'a weekend or holiday, the no need to include the start date, otherwise if
    'the start date is a workdate and the user specified to include it, then
    'adjust for that situation.
    '...Order of the Case statements is critical
    Select Case True
            
        Case Weekday(dtStartDate, vbSaturday) <= 2, blStartIsHoliday
            If dtStartDate = dtEndDate Then
                lngAdjustment = 0
            Else
                lngAdjustment = Not blIncludeStartdate
            End If
                        
        Case blIncludeStartdate
            lngAdjustment = 1
    
    End Select

    'Return the result
    fNetWorkdays = (lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment)
    If dtStartDate > dtEndDate Then
        fNetWorkdays = 0 - fNetWorkdays
    End If
    
End Function
    
    
Public Function fAddWorkdays(dtStartDate As Date, _
                             lngWorkDays As Long) _
                             As Date
'Adds the passed number of workdays to a passed date.  This code uses
'fNetWorkdays(), so the assumptions of tblHoliday apply for this function
'as well. Also note that if a ZERO is entered as the lngWorkDays parameter
'the function will return the start date, if its a work day, or the first
'workday PRIOR to the dtStartdate.
'''''''''''''''''''''''''''''''''''''''''''
'Author: Brent Spaulding
'Version: 7
'Date: Aug 8 2008
'''''''''''''''''''''''''''''''''''''''''''
'Revision History:
'Ver    Description
'?-4    Intial releases to UA in various threads and the Code Archive
'5      Made the function cabable of handling negative work days to add
'6      Corrected for a DIV by Zero error when 0 was entered as lngWorkdays
'       as well as some buggy stuff with negative workdays
'7      Formated date literals to corrected for possible errors with
'       NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
'..........................................
    
    Dim dtEndDate As Date
    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngOffset As Long
    Dim lngSundays As Long
    
    'First ... GUESS at the End Date you need to cover the workdays you are adding.
    'I ASSUME that the number of days that are added will always toss you into a
    'week end, then I add the number of work weeks to it the get the number of
    'saturdays and sundays.
    lngSaturdays = 1 + Abs(lngWorkDays) \ 5
    lngSundays = lngSaturdays
    
    dtEndDate = DateAdd("d", Sgn(lngWorkDays) * (Abs(lngWorkDays) + lngSaturdays + lngSundays), dtStartDate)
    
    'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
    'of days requested.
    Do Until lngWorkDays = lngDays
    
        'Count the number of work days between the ESTIMATED end date
        'and the start date
        lngDays = fNetWorkdays(dtStartDate, dtEndDate, False)
    
        'Make an adjustment to the end date
        If lngDays <> lngWorkDays Then
            lngOffset = lngWorkDays - lngDays
            dtEndDate = dtEndDate + lngOffset
        End If
    
    Loop
    
    'Determine the offset direction to adjust for weekends and holidays
    'the offset trys to bring the end date CLOSER to the start date.
    If lngWorkDays < 0 Then lngOffset = 1 Else lngOffset = -1
    
    'Make sure the end day is NOT a holiday and NOT a Saturday/Sunday
    Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
                                " And Weekday([HolidayDate],1) Not In (1,7)") = 0 _
             And Weekday(dtEndDate, vbMonday) < 6 '6th day of week if Mon is first day
        dtEndDate = dtEndDate + lngOffset
    Loop
    
    'Once we are out of the loop, the end date should be set to the correct date
    fAddWorkdays = dtEndDate
    
End Function

Many thanks to all who contributed. Let us hope we have all learned from this experience. I certainly have.

 

[jdraw]

Glad you have it resolved.
In the end you used the function you found.

The function I created and suggested is what I used to get the answer to your questions. I used the holidays you provided in the earlier posts. And I think Chris (stopher) did as i suggested to you--copy my function, set up your holidays and use a small test.

I did not use the function you found.

It seems you have solved your immediate issue.
We are here to help/advise as you move forward.

Watch some of the Steve Bishop videos I mentioned for clarification on vba and related usages.
You may also benefit from video #1,2 and 4 from the Database series by Dr. Daniel Soper. They start here.

Good luck.