Date formula to count days between two dates but not count holidays or weekends

[Scholar81]

Hello,

I am a novice Access developer and my boss asked me to build a database and I said yes. Now I realized the bite is way too big and I'm trying not to choke ;-)

And what's worse, he does not want me to use VBA (thank god, one less thing for me to tear my hair out about). So I am struggling to figure out how to do this:

Count the number of days between two dates and not count holidays and weekends.

I googled it up and talked with a couple of folks, they all said VBA is the key but my boss says it can be done without VBA.

Do anyone know how I can do this without using VBA? any help would so much appreciated. Maybe I will finish the project with some hair intact!

Matt

[Denburt]

All I can do is hope I can help, especially when it comes to the hair, I know how it feels to lose it.

Do you have a table set up for the Holidays or anything? If so please display your table structures and relationships.

Do you have a Holidays table? If not I think we should start with the holidays table and go from there. Set up a table with an autonumber field for a primary field then list your holiday names and a field for the Holiday dates (you may not want to use this as a date field since some holidays might fall on the first Monday of a month etc. so try to take this all into account.

[Scholar81]

Thank you so much cuz, I love my hair ;-)

In Access 2007:

I already set up a holiday table with all holidays from 2007 to 2012. however, I use data/time field for all holiday dates so should I change it to text field? Also, I looked at the relationship and there's no connection to any forms, queries or forms. It's empty.

I know the next step is to create a holiday query but after that I am completely lost to what to do.

[Denburt]

Count the number of days between two dates and not count holidays and weekends.

Well you didn't provide a table structure so... Are the two dates in one table on two different records or is it in one table in two different fields, or are the dates in two different tables?

Quick stab, if the two dates are in one table in two different fields try this as a start for your select query. Note this is only to determine if either date falls on a weekend, if so we need to make an adjustment for that however if you are sure neither date falls on a weekend then we can skip this (let me know). Just go to SQL view on a new query and modify the following as necessary:

Select IsItaWeekDay:iif(weekday(Date1)=1 or weekday(Date1)=7,iif(weekday(Date2)=1 or weekday(Date2)=7,"Weekend","Not") From YourTable

[NeoPa]

@Scholar81
It can Matt, but not without stored holidays, and not remotely easily. It doesn't sound like your boss has a very good understanding of the complications involved. I wouldn't even want to take anyone but an experienced programmer (or DB designer) through this to be perfectly honest.

On the plus side, though it's easier using VBA, it's not a whole hell of a lot easier to be fair.

This problem comes up fairly frequently. Often introduced by "I know this must be easy but ...".

The technique would be to determine (arithmetically) how many weekdays exist between the two dates. From this you would need to subtract those holidays found in your holiday table which both :

1. Occur on a weekday.
2. Occur between the start and end dates.

[NeoPa]

@Scholar81
Date/Time is probably better.

It may help if you post the Meta-Data (info about the layout / structure) for your table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[Scholar81]

I am sorry... I forgot to include the file structure and everything.

All pertinent date fields are in one table "Data Collection". The pertinent date fields names are [CAR Discovered Date] and [CAR TO EVMC Date (initial)]

The holiday table is titled Holidays
The query holiday table is titled qryHolidays

So based on the formula you gave me.

I am comfortable with design view so I'll just do it from there instead of SQL view if you dont mind ;-) I am so new at this.

I go to the Data Collection query table and input this in a new column. Weekday:

Weekday: iif(weekday[CAR TO EVMC Date (initial)])=1 or weekday([CAR TO EVMC Date (initial)])=7, iff(weekday[CAR Discovered Date])=1 or weekday([CAR Discovered Date])=7, "weekend", "Not")

That makes total sense. #1 is sunday while #7 is saturday because Access by default starts the week on Sunday or 1.

I knew iif was the key but wasnt sure how to write it. Think of me as your 28 year old 1st grade student struggling to learn English ;-)

I hope we can do this tonight so I can tell my boss tmw morning that I did it (sort of)

Matt

[NeoPa]

I'm off for tonight, but before I go you guys should know that the Weekday() function takes a parameter whereby you can specify the start day of the week. Checking for weekend days can be done in a single check (<2) if you specify Saturday as the week start.

Have fun.

[FishVal]

HowTo: Calculate business days - pure SQL approach.

[NeoPa]

Nice work Fish :)

I've sent you a PM in relation to this (The article - not this thread per se).

PS. I've also locked the article thread to avoid more nooby posters dropping their questions in there in future (They can post a new thread with a link possibly, as they're supposed to do).