How To Calculate Working Days?

[Moonshine]

Is there a formula in a Query builder for calculating the amount of WORKING days between two date fields?

So between 1st August 2003 and 15th August, there is a total of 15 days, but only 11 of those are Working Days.... as we dont want to include weekends.

Ta

Scott

 

[MattS]

You will have to create a custom function. Do a search on this forum, specifically for posts by Mile-O-Phile.

This has come up numerous times, and Mile-O has provided many (great) examples.

 

[Fornatian]

This is a good example...

 

[Mile-O]

Code and a .zip file on this thread.

Since you're in England it'll give you a grasp of how to also dismiss all of the English bank holidays and other days off without having to know them beforehand.

Just click here...

 

[Moonshine]

Oh this is excellent!

Thanks for this everyone, this is going to get used alot. As we are a .gov organisation, we NEVER use weekends in any of our calculations, and even up manualy excluding them using coff horrible excel coff splutter.

Scott

 

[Mile-O]

As we are a .gov organisation, we NEVER use weekends in any of our calculations, and even up manualy excluding them using coff horrible excel coff splutter.

Have you never seen the NETWORKDAYS function in Excel?

 

[Moonshine]

I spend as little time as possible using excel. I guess now i should try looking at it more...

 

[Mile-O]

It's in the Excel Analysis Toolpak.

Goto Tools -> Add Ins and check the Analysis Toolpak, and hey presto! loads more functions.

 

[Moonshine]

Cool

There is an Analysis Toolpack - VBA do i need that one too?

 

[Mile-O]

If you are coding with VBA in Excel then yes.

 

[Moonshine]

I've never tried, but i might have a play Is it vastly different from coding within Access?

 

[Moonshine]

This is a good example...

Formation, this is exellent but i have one question.... when i call the function in the query builder, and tell it which fields i want calculating, it results it numbers.... but if in the criteria of that, i put <8 (i want all records where there are 7 or less days between the two dates, it comes back with Data Type mismatch...

Is the number it returns not really a number? If not, can it be?

 

[Mile-O]

Just the objects. Record a few macros and have a look at the VBA they produce.

 

[Moonshine]

Ta Mile, might give that a try

 

[Fornatian]

I've just tested the qry qselDateDifferenceDemonstration in the demo I posted with criteria and it works on my PC using the following SQL.

SELECT tblDemonstrationData.dtDay1, tblDemonstrationData.dtDay2, funWorkDaysDifference([dtDay1],[dtDay2]) AS WorkDaysDifference
FROM tblDemonstrationData
WHERE (((funWorkDaysDifference([dtDay1],[dtDay2]))<8));

See the attachment for what my query window looks like

 

[Moonshine]

Yeah, that's what i had. Can only be a problem with my two date fields then.

Cheers for the help, back to the drawing board

 

[CraigBFG]

The working days function works great from within Access itself, but how do I get this to work from a website - calling the db via sql statements. I keep getting undefined function.

Please help.

 

[halmeida571]

This is a good example...

That example was help me today thanks a lot