up vote 1 down vote favorite
share [g+] share [fb]

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

link|improve this question
73% accept rate
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
feedback

2 Answers

up vote 2 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

link|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
feedback
up vote 0 down vote

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

link|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
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.