4

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

Improve this question
4
  • this can't be moved with a bounty. sorry. :(
    – Jeff Atwood
    Sep 15, 2009 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, 2009 at 21:38
  • Cheers, thought the bounty may interfere with things.
    – Lunatik
    Sep 16, 2009 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, 2009 at 15:22

2 Answers 2

Reset to default
5
+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

Improve this answer
1
  • Excellent, this works well. I had tried this kind of thing but got bogged down in the logic. Ta muchly :)
    – Lunatik
    Sep 17, 2009 at 9:31
0

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

Improve this answer
3
  • This would work from with Access where custom functions are exposed, but AFAIK this wouldn't work over an ADO connection.
    – Lunatik
    Sep 17, 2009 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, 2009 at 14:26
  • Sorry, had to vote down - link takes me to a google site that asks me to log-in.
    – Darren Bartrup-Cook
    Nov 16, 2017 at 11:20

You must log in to answer this question.

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