asked on

Excel NetworkDays function for Access

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

ASKER CERTIFIED SOLUTION

adraughn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adraughn

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)

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

also, see this link
http://www.mvps.org/access/datetime/date0012.htm