[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Excel NetworkDays function for Access

Posted on 2007-03-28
Medium Priority
1,907 Views
Does anyone know how to make a function for SQL that will behave the same way as Excel's networkdays.  Here is my excel formula

=IF(K125=TODAY(),0,NETWORKDAYS(K125,TODAY()-1))

Note*  If K125 = 03/28/07 which is today, so the result would be 0 or If K125=03/26/07, the result would be 2

0
Question by:cstraim
[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
• 2
• 2

LVL 13

Accepted Solution

ID: 18809648
Put this in a module. When passed two dates it will eliminate weekends.

Option Compare Database
Option Explicit

Public Function DateDiffW(BegDate, EndDate) As Integer
On Error Resume Next
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function
0

LVL 13

Expert Comment

ID: 18809689
For SQL:

@bDate datetime
Set @bDate = '5/12/2005'

Select
estraWeekDays = case
else 0 end,
else 0 end)
0

LVL 120

Assisted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 18809944
try this function

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer

Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NetworkDays(startDate, endDate)
Set objFunction = Nothing
End Function

Note**

Tools>References

Microsoft Office Web Components Function Library
0

LVL 120

Expert Comment

ID: 18809981
0

## Featured Post

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retrâ€¦
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.