UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Working Day Calculation Issue When Start Date Is Weekend., Access 2010    
 
   
BOFH
post Jun 10 2019, 10:48 AM
Post#1



Posts: 6
Joined: 10-June 19



Hi Hoping someone can help me.

Im new to VBA and my skills are enough to modify code to my purpose and write very basic code. Ive been using the following code from our old database to calculate working days in my MI system. This has historically worked perfectly as our company works Monday to Friday, so almost all calculation are based around this. However im using the code now to calculate working days in a month to use on an excel report and have found a bug that's beyond my skills to resolve.

If the start day falls on a Saturday or Sunday it will calculate this as a working day so when applied to June I get 21 working days. How do I modify the code to prevent this?

Code as follows.

Thanks in advance

Public Function Is_WorkingDays(Start1, End1)

' calculate the number of Working days between passed dates


Dim sub1 As Integer
Dim WDayCount
Dim nextdate As Date
Dim rs As Recordset

On Error GoTo Is_WorkingDaysErr


WDayCount = 0


If IsNull(End1) Then
Is_WorkingDays = 0
Else

For sub1 = 1 To DateDiff("d", Start1, End1)
nextdate = DateAdd("d", sub1, Start1)
Select Case DatePart("w", nextdate)
Case vbSunday, vbSaturday
Case Else
If Is_BankHoliday(nextdate) Then
Else
WDayCount = WDayCount + 1
End If
End Select
Next sub1
Is_WorkingDays = WDayCount
End If

Exit Function

Is_WorkingDaysErr:

Is_WorkingDays = 0
' is_Application_Error Err, "StandardFunctions", "is_WorkingDays"
Exit Function

End Function

This post has been edited by BOFH: Jun 10 2019, 11:34 AM
Go to the top of the page
 
theDBguy
post Jun 10 2019, 10:55 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Not sure I understand completely. Are you saying Saturday or Sunday is a working day now? Or, are you saying the code is counting Saturdays as a working day, and it's not supposed to.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
BOFH
post Jun 10 2019, 11:02 AM
Post#3



Posts: 6
Joined: 10-June 19



Hi

Sorry, should have explained better.

If the Start Date is a Saturday or Sunday the function counts it as a working day. Otherwise it works perfectly.

So when Start1 is 01/06/2019(Saturday) and End1 is 30/06/2019 I get 1 working day too many.

But

When I run it for Last month 01/05/2019(Wednesday) to 31/05/2019 its perfect.

Ive tested it on several months and shorter periods and whenever the Start1 is a Saturday or Sunday it gets counted as a working day for the result. I assume the function automatically calculates Start1 as 1 and then adds the additional working days to it.

Im using the function to add working days to an excel report in two places and if the Start1 date for either is a Saturday or Sunday the working day count is always 1 too high.

Thanks

Go to the top of the page
 
theDBguy
post Jun 10 2019, 11:13 AM
Post#4


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Okay. Thanks for the clarification. I think you have two options: (1) You could try modifying the code to not count the start day if it's on a weekend or, (2) just subtract one day from the result if the start date is on a weekend.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
BOFH
post Jun 10 2019, 11:19 AM
Post#5



Posts: 6
Joined: 10-June 19



Thanks

I was thinking option 1, which to be honest is way beyond what I can do as I don't understand the function well enough to edit it to that level and I need something automatic as I cant rely on the users to edit the number when needed.

But option 2 is an idea I should be able to add an If somewhere in the code to -1 when the start date is a Saturday or Sunday. Ill do some Googling in the morning and work it out I hadn't looked at the problem that way round smile.gif

Thanks for your help.
Go to the top of the page
 
theDBguy
post Jun 10 2019, 11:25 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Hi. You can use the Weekday() function to determine if a date is on a Saturday or Sunday.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Jun 10 2019, 11:27 AM
Post#7



Posts: 2,689
Joined: 4-February 07
From: USA, Florida, Delray Beach


The following Revision should do the trick. It includes both the Start and End Dates and does NOT factor in Bank Holidays:
CODE
Public Function Is_WorkingDays(Start1, End1)
'Calculate the number of Working days between passed dates
Dim sub1 As Integer
Dim WDayCount
Dim nextdate As Date


WDayCount = 0

If IsNull(End1) Then
  Is_WorkingDays = 0
Else
  For sub1 = 0 To DateDiff("d", Start1, End1)
    nextdate = DateAdd("d", sub1, Start1)
      Select Case Weekday(nextdate)
        Case vbSunday, vbSaturday
        Case Else
          'If Is_BankHoliday(nextdate) Then
          'Else
            WDayCount = WDayCount + 1
          'End If
      End Select
  Next sub1
  
Is_WorkingDays = WDayCount
End If
End Function

This post has been edited by ADezii: Jun 10 2019, 11:27 AM
Go to the top of the page
 
BOFH
post Jun 10 2019, 11:31 AM
Post#8



Posts: 6
Joined: 10-June 19



I assume if I just reinclude the bankholiday part, it will then factor in the bankholidays too?

Thanks for the help smile.gif
Go to the top of the page
 
ADezii
post Jun 10 2019, 11:35 AM
Post#9



Posts: 2,689
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I assume if I just reinclude the bankholiday part, it will then factor in the bankholidays too?

Yes, as long as the Logic is sound.
Go to the top of the page
 
BOFH
post Jun 11 2019, 04:08 AM
Post#10



Posts: 6
Joined: 10-June 19



That's great thanks very much. All fixed.

It was fixed so well it removed an old bug of consistently undercounting by one day! So ive had to add that issue back in to save changing code in multiple places as I automatically compensate for it! smile.gif

Thanks again for you help
Go to the top of the page
 
cheekybuddha
post Jun 11 2019, 04:45 AM
Post#11


UtterAccess VIP
Posts: 11,696
Joined: 6-December 03
From: Telegraph Hill


Surely this is a job for the PFY? wink.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
BOFH
post Jun 11 2019, 04:52 AM
Post#12



Posts: 6
Joined: 10-June 19



If only I had one smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 11:05 AM