Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 3 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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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