?
Solved

Calculate Business Days

Posted on 2008-10-24
7
Medium Priority
?
797 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 2000 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
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.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

719 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