query calc for date difference - novice (1 Viewer)

[felicia.scott@]

Greetings,

I am a novice query builder and seek formula assistance to calculate the difference between two dates while also excluding weekend dates from the calculation.

I found a helpful MasterDates database here that provides the weekend dates to exclude however, I do not know how to integrate it to the formula in the query.

Table 1: Project details has field for date a project was assigned and the date a project was actually started.

Query: Masterdates list of all weekend/holidays hence forth known as non productive days

:banghead:My current formula: [project assigned]-[project started]
ex: project assigned 12/31/2017 - project start 1/3/2018

Result would be 3 but I need it to reflect the actual number of days as 2 since 12/31/17 was a Sunday

Any help in laymen's terms is greatly appreciated. I am not familiar with SQL

 

[pbaldy]

Typically a custom function is used that loops through the input dates and checks for weekends and holidays. Search here on business days or weekdays. I personally wouldn't bother with a table for weekends since those are easily calculated. A table for holidays is common though.

 

[felicia.scott@]

I found a couple that could work but the error says they are from an earlier version of Access and doesn't allow me to open. The help suggest compacting them but again, I can't get them open to compact.

I see the question has been posed ad nausem but I am still stuck.

 

[felicia.scott@]

Found the answer... thank you. Thank you. THANK YOU... for anyone else, here is the non-SQL how to...

NumberOfWorkingDays:
[EndDate]-[StartDate]+1
-(DateDiff("ww",[StartDate],[EndDate],7)-(Weekday([StartDate])=7))
-(DateDiff("ww",[StartDate],[EndDate],1)-(Weekday([StartDate])=1))

.

 

[pbaldy]

You realize that doesn't account for holidays, just weekends?