Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
    
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
1  
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
up vote 4 down vote accepted
+100

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

share|improve this 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.

share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .