Solved

Calculate Business Days

Posted on 2008-10-24
7
787 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now