Home » Questions » Computers [ Ask a new question ]

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: 27
Total answers/comments: 1
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"