Equivalent of Excel's NETWORKDAYS function with Jet ADO

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?
Asked by: Guest \| Views: 158

Total answers/comments: 1

Comments display order:

Guest [Entry]

"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"