Solved

Calculate Business Days

Posted on 2008-10-24
7
793 Views
Last Modified: 2013-11-27
The code below displays #Error in a query when the StartDate or EndDate is null.  How can I modify the code to say: "if the StartDate or EndDate is null, then WorkingDays = null"  I've tried to incorporate this logic unsuccessfully.  Any ideas on how to get this logic to work?   Note: Much credit to Arvin Meyer for the below code.

Thank you,

Troy
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
'   EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'...................................................................
 
 
 
On Error GoTo Err_WorkingDays
 
Dim intCount As Integer
 
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
 
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
 
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
 
Exit_WorkingDays:
Exit Function
 
Err_WorkingDays:
Select Case Err
 
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
 
End Function

Open in new window

0
Comment
Question by:T1080
  • 4
  • 3
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22801711

if you want to get a return value of null the function must be of type variant

better to use vartype function to check for real date

 
Public Function WorkingDays(StartDate, EndDate) As variant
'....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
'   EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'...................................................................
 
 
 
On Error GoTo Err_WorkingDays
 
Dim intCount As Integer
 
If vartype(StartDate)<>7 or vartype(EndDate)<>7 then
    WorkingDays=null
    exit function
end if
 
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
 
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
 
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
 
Exit_WorkingDays:
Exit Function
 
Err_WorkingDays:
Select Case Err
 
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
 
End Function

Open in new window

0
 

Author Comment

by:T1080
ID: 22801741
capricorn1:

That works great. Can you explain the logic as to why variant is preferrable to use over integer in this scenario?  When the type was integer I tried to use the code:
if  isnull(StarDate) or isnull(EndDate) then WorkingDays =null
Exit Function
End if
This code gave me a compile error saying: : "End If without block If" Why is this?  It is almost as if it would not recognize if statements.

Also, what does vartype (StartDate)<>7 mean logically?  I've never used this code before.

Thank you,

Troy
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22801753
variant type will accept a value of  { Null } or number or a string

vartype function is an array of type variant.

the value of 7 is for Date

from vba help type varType for more info
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:T1080
ID: 22801775
capricorn1:

I tried to modify my code to match your changes.  I changed integer to variant and then added this code:
If vartype(StartDate)<>7 or vartype(EndDate)<>7 then
    WorkingDays=null
    exit function
end if
I still get an #Error in my values.

When I delete my code and paste in your code then everything works fine.  What am I missing?

Troy
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22801788
in your codes, aside from adding  the checking, i just alter the first line

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

to

Public Function WorkingDays(StartDate, EndDate) As variant


can't you see the difference
0
 

Author Comment

by:T1080
ID: 22801789
I got it.  You also removed as date from the public function line.
0
 

Author Closing Comment

by:T1080
ID: 31509894
I appreciate the help.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question