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?

Lunatik

Posted 2009-09-04T13:00:56.117

Reputation: 4 973

this can't be moved with a bounty. sorry. :( – Jeff Atwood – 2009-09-15T21:37:08.923

I suggest asking this on SO with more background explanation -- as in, what the heck does NETWORKDAYS do? – Jeff Atwood – 2009-09-15T21:38:48.920

Cheers, thought the bounty may interfere with things. – Lunatik – 2009-09-16T07:25:54.793

1NETWORKDAYS 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 – 2009-09-16T15:22:07.167

Answers

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

JDunkerley

Posted 2009-09-04T13:00:56.117

Reputation: 504

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

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

DaveParillo

Posted 2009-09-04T13:00:56.117

Reputation: 13 402

Sorry, had to vote down - link takes me to a google site that asks me to log-in. – Darren Bartrup-Cook – 2017-11-16T11:20:11.323

This would work from with Access where custom functions are exposed, but AFAIK this wouldn't work over an ADO connection. – Lunatik – 2009-09-17T10:02:57.553

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 – 2009-09-17T14:26:00.760