Calculate workdays (and defined holidays) between two dates (1 Viewer)

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
Having a spot of bother with the code.

I'm nearly there (other than the fact that it's completely wrong !!)

Code:
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

Any glaring errors !!

Example test calculate between 01/08/2016 and 31/08/2016 with one holiday (29/08/2016) should return 20. I'm getting 22 !!
 

plog

Banishment Pending
Local time
Today, 06:35
Joined
May 11, 2011
Messages
11,613
Any glaring errors !!

Yes, but not in the code, in your expectations:

Example test calculate between 01/08/2016 and 31/08/2016 with one holiday (29/08/2016) should return 20. I'm getting 22 !!

On what are you basing 20 as the correct answer? I just opened a calendar and manually counted and got 22 using your criteria.
 

Ranman256

Well-known member
Local time
Today, 07:35
Joined
Apr 9, 2015
Messages
4,339
I seems you wouldn't need this code.
It would be 3 queries,
1 query to calculate workdays
1 query to count the days in the holiday total (as negative)
1 query to sum the two.

No need to cycle thru using FindNext.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:35
Joined
Oct 17, 2012
Messages
3,276
Plog is correct. There were 23 weekdays in August this year, and if you discount one as a holiday, then 22 is the correct result.
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
The word "between" is A MASSIVE clue !!!!

OK Accepting the fact that you didn't read my post properly and that the result of the function includes both of the dates by which I'm endeavouring to make the calculation.

It's still incorrect.

Start Date 01/12/2016

Holidays 26/27/28/29/30th and 2nd January

End Date 07/01/2017

result = 20 wheras it should be 19

The second Example:
02/12/16 to 23/12/16 should be 16 and I get the correct answer.
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:35
Joined
Oct 17, 2012
Messages
3,276
The word "between" is A MASSIVE clue !!!!

That you don't understand this code, perhaps.

The comments in the code explicitly indicate that you're including both start date and end date.
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
Der. Of course I don't understand the code !!! I'm winging it like everybody else I just keep bumping into smart arse codies that spend more time in a holier-than-thou world of pomposity waiting for the unbelievers to fall from grace.

Stultus est sicut stultus facit
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:35
Joined
Oct 17, 2012
Messages
3,276
Der. Of course I don't understand the code !!! I'm winging it like everybody else I just keep bumping into smart arse codies that spend more time in a holier-than-thou world of pomposity waiting for the unbelievers to fall from grace.

Stultus est sicut stultus facit

And perhaps you'd have a bit better luck if you weren't quite so offensive when the first couple replies weren't the specific answer you're looking for.
Even after your smart-assed comment I pointed you toward the issue. Had you bothered to look rather than whine about people being mean to you, you'd have resolved the issue already.

Good luck with your project.
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
Ranman:

I can calculate the amount of weekdays between two dates but Im struggling with counting the holidays between two dates.

Could you point me in the right direction?
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:35
Joined
Jan 23, 2006
Messages
15,364
General approach.


SELECT Count([HolidayDate]) FROM tblHolidays
where HolidayDate BETWEEN StartDate AND EndDate
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
jdraw. Many thanks for the link.

I have tried this and again it fails with my test data: returning 20 days when it should be 19.

Do you think it has something to do with going into a new year (ie one of the holiday dates in 03/01/2017)
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:35
Joined
Jan 23, 2006
Messages
15,364
I just ran a test based on your post #1,
Working days between Aug 1 and Aug 31 2016 where Aug 29 2016 is a Holiday.
(weekend is Sat/Sun)

Code:
Sub testECEK()
Dim startdate As Date: startdate = #8/1/2016#
Dim enddate As Date: enddate = #8/31/2016#
Debug.Print fWorkingDays(startdate, enddate)
End Sub
Holiday table
Code:
HolidayDate	id
25-Mar-2016	1
28-Mar-2016	2
17-Mar-2016	3
29-Aug-2016	4

Result 22.
 

Attachments

  • ECEKHolidays.jpg
    ECEKHolidays.jpg
    98 KB · Views: 224

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
jdraw could you please run an additional test between

01/12/2016 and 04/01/2017

Where the holidays =
26/12/2016
27/12/2016
28/12/2016
29/12/2016
30/12/2016
02/01/2017
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:35
Joined
Jan 23, 2006
Messages
15,364
Code:
Sub testECEK()
Dim startdate As Date: startdate = #12/1/2016#  'm/d/y US
Dim enddate As Date: enddate = #1/4/2017#
Debug.Print fWorkingDays(startdate, enddate)
End Sub

Holidays
Code:
HolidayDate	id
25-Mar-2016	1
28-Mar-2016	2
17-Mar-2016	3
29-Aug-2016	4
26-Dec-2016	5
27-Dec-2016	6
28-Dec-2016	7
29-Dec-2016	8
30-Dec-2016	9
02-Jan-2017	10

result: 19

Good luck.
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
Thanks jdraw.

My problem (so suitably exposed by frothyshit) is that all I have done is copied the code into a module then called it from a query.

DateRqd:WorkingDays2([StartDate],[EndDate])

and I get 20 !!

Let me apologise to all members who feel it necessary to belittle those of us who are under extreme pressure in other aspects of our jobs that we didn't get round to learning VBA in its entirety (or at all, in my case !!) I am learning from your posts when and where I can.

I thank nearly all of you for your contributions.

Now then
-----------------------------------------------------------

What's "Dumb Cluck" in Latin?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:35
Joined
Oct 17, 2012
Messages
3,276
Let me apologise to all members who feel it necessary to belittle those of us who are under extreme pressure in other aspects of our jobs that we didn't get round to learning VBA in its entirety (or at all, in my case !!) I am learning from your posts when and where I can.

I thank nearly all of you for your contributions.

Perhaps you will get friendlier responses in the future if your immediate replies to people not giving you precisely the answers you want to hear are something other than condescension and personal attacks. If you insist on behaving that way, then you're going to need a thicker skin, because some people WILL respond in kind, and you'll find ever fewer people willing to answer your questions.
 

ECEK

Registered User.
Local time
Today, 11:35
Joined
Dec 19, 2012
Messages
717
Having explained my position: Apologised: You still find it necessary to provoke. Even when I'm on my knees begging for forgiveness due to not knowing how to use VBA....

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)
 

stopher

AWF VIP
Local time
Today, 11:35
Joined
Feb 1, 2006
Messages
2,396
Having explained my position: Apologised: You still find it necessary to provoke. Even when I'm on my knees begging for forgiveness due to not knowing how to use VBA....
Must have missed that. Did you mean this:

Let me apologise to all members who feel it necessary to belittle those of us who are under extreme pressure in other aspects of our jobs that we didn't get round to learning VBA in its entirety (or at all, in my case !!) I am learning from your posts when and where I can.

I thank nearly all of you for your contributions.

So using the word "apologise" in the same sentence as berating the people you are apologising to? Surely an apology has to come with sincerity.

And earlier in the same post...
My problem (so suitably exposed by frothyshit)....
Rather provocative don't you think.
 

Users who are viewing this thread

Top Bottom