Solved

Calculate Business Days

Posted on 2008-10-24
7
796 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

626 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