Solved

Excel NetworkDays function for Access

Posted on 2007-03-28
4
1,880 Views
Last Modified: 2012-06-21
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
Comment
Question by:cstraim
  • 2
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
adraughn earned 250 total points
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

by:adraughn
ID: 18809689
For SQL:

Declare @aDate datetime,
      @bDate datetime
Set @aDate = '4/22/2005'
Set @bDate = '5/12/2005'

Select
      dayDiff = datediff(dy,@aDate,@bDate),
      fullweeks = datediff(dy,@aDate,@bDate)/7,
      weekdays = datediff(dy,@aDate,@bDate)/7*2,
      startday = datepart(dw,@aDate),
      extraDays = datediff(dy,@aDate,@bDate)%7,
      estraWeekDays = case
                  when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
                  when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
                  when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
                  else 0 end,
      weekdayDiff = datediff(dy,@aDate,@bDate) - (datediff(dy,@aDate,@bDate)/7*2 +
                  case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
                  when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7=6 then 2
                  when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
                  when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
                  else 0 end)
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 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**
you have to add to your references;

Tools>References

                 Microsoft Office Web Components Function Library
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18809981
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 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

11 Experts available now in Live!

Get 1:1 Help Now