Solved

Calculate Business Days

Posted on 2008-10-24
7
784 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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 119

Expert Comment

by:Rey Obrero
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now