Is there a way of replicating Excel's NETWORKDAYS when connecting to a Jet database via ADO?
2 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
-
Excellent, this works well. I had tried this kind of thing but got bogged down in the logic. Ta muchly :)– LunatikSep 17, 2009 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.
-
This would work from with Access where custom functions are exposed, but AFAIK this wouldn't work over an ADO connection.– LunatikSep 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. Sep 17, 2009 at 14:26
-
Sorry, had to vote down - link takes me to a google site that asks me to log-in. Nov 16, 2017 at 11:20
NETWORKDAYS(start_date,end_date,holidays)