# Equivalent of Excel's NETWORKDAYS function with Jet ADO

Is there a way of replicating Excel's NETWORKDAYS when connecting to a Jet database via ADO?

-
this can't be moved with a bounty. sorry. :( –  Jeff Atwood Sep 15 '09 at 21:37
I suggest asking this on SO with more background explanation -- as in, what the heck does NETWORKDAYS do? –  Jeff Atwood Sep 15 '09 at 21:38
Cheers, thought the bounty may interfere with things. –  Lunatik Sep 16 '09 at 7:25
NETWORKDAYS is a function from the Analysis ToolPak add-in. It returns the number of whole working days between a start date and end date. It excludes weekends and any dates identified in holidays, which you have to pass in as an argument. Function signature is `NETWORKDAYS(start_date,end_date,holidays)` –  DaveParillo Sep 16 '09 at 15:22

First assuming we know both the start and end are weekdays then, I think this works:

``````([DateEnd]-([DateStart]+(Weekday([DateEnd])-Weekday([DateStart]))))/7*5+(Weekday([DateEnd])-Weekday([DateStart]))+1
``````
• work out number of weeks between the dates and times by 5.
• add difference of the weekday
• add one to include both start and end

If you dont know that both are work days you need a correction. I think this is correct:

``````([DateEnd]-([DateStart]+(Weekday([DateEnd])-Weekday([DateStart]))))/7*5+(Weekday([DateEnd])-Weekday([DateStart]))+1+IIf(Weekday([DateEnd])=7,-1,0)+IIf(Weekday([DateStart])=1,-1,0)
``````

I tested it against Excel, and it appears to give the correct answer

-
Excellent, this works well. I had tried this kind of thing but got bogged down in the logic. Ta muchly :) –  Lunatik Sep 17 '09 at 9:31

There's also this function. It is algorithmically nearly identical to @JDunkerley's - just more verbose. Possibly easier to implement in different environments.

-
This would work from with Access where custom functions are exposed, but AFAIK this wouldn't work over an ADO connection. –  Lunatik Sep 17 '09 at 10:02
Agreed. i thought the post was useful because it was the same algorithm implemented differently, the sort of thing you could use as a back check, etc. –  DaveParillo Sep 17 '09 at 14:26