Calculating Number of Work Dates From 2 Date Fields (1 Viewer)

[sara82]

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?


Any help will be appreciated, thanks

 

[sonny]

Take a look at this db...

 

[sara82]

Thank you Sonny,

But is there anyway I can do this in a query.. just calcuating the number of work days excluding weekends?

 

[ColinEssex]

Sara, have a look here

Col

 

[sara82]

Thanks so much for the reference. That helped out greatly.

I noticed there were 2.. one to calcuate work days and another modified to account for holidays.

I used the 1st one to test it out and it worked out.. so then I tried the other one the one that accounts for holidays since that is what I really need.

In my query I have the following as a field
NoOfDays: workingdays2([StartDate],[EndDate])

But when I run my query it gives me the error:
Compile Error: User-Defined type not defined

It higlights rst As DAO.Recordset in the module code.

How can I make this work?

 

[Jon K]

But when I run my query it gives me the error:
Compile Error: User-Defined type not defined

It higlights rst As DAO.Recordset in the module code.

To use DAO, you need to make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO 3.6 Object Library.)

.

But is there anyway I can do this in a query..

See the query in the sample database. It uses an expression to calculate the number of work days excluding holidays:

NumberOfWorkDays: [EndDate] - [StartDate] - (DateDiff("ww", [StartDate], [EndDate], 7) - (Weekday([StartDate]) = 7)) - (DateDiff("ww", [StartDate], [EndDate], 1) - (Weekday([StartDate]) = 1)) - (Select Count(*) from [tblHolidays] where [HolidayDate] between [StartDate] and [EndDate]) + 1


It runs faster than using VBA to open the holiday table.

In the sample data, I have made 5/16/2005 a holiday for testing purpose.
.

 

[sara82]

Jon K,

Thank you so much, it's exactly what I was looking for. I really appreciate it.

 

[sonny]

Jon K, then is it also posible to include yet another table having leave dates as you did for holidays? That way it takes out those dates also....

 

[sara82]

I have an additional question that has risen.

When I run the query everything works perfectly.

When i try to create report with that field NumberOfWorkDays included I keep getting the following error:

"Multi-level GROUP BY clause is not allowed in a subquery."

I am using grouping by a particular field. And as a report footer I have where it calcuates the sum of a particular field and a count of a particular field. My report was also working fine before adding the NumberOfWorkDays.

Is there anyway around this, where I can display NumberOfWorkDays in my reports?

 

[Jon K]

Sonny,

If each leave date is stored as one record in a leave day table like the holiday table in my sample, it is possible. You just add one more select statement in the expression:

- (Select Count(*) from [tblLeaveDays] where [LeaveDate] between [StartDate] and [EndDate])


If the leave days are stored in two fields [LeaveStart] and [LeaveEnd], then it's too much for an expression in a query to handle. You'll need VBA code in a public function.



Sara,

In my expression,

(Select Count(*) from [tblHolidays] where [HolidayDate] between [StartDate] and [EndDate])

is a subquery.


You may try replacing it with DCount():

DCount("*","[tblHolidays]","[HolidayDate] between DateValue('" & [StartDate] & "') and DateValue('" & [EndDate] & "')")


In the query, DCount() runs slightly slower than Select Count(*).
.

 

[sonny]

I adopted code that creates a temp table(maketbl) of all dates from the start and end dates. Then I run a append qry to insert the temp tbl data into the master tbl. So I have all the dates in a seperate tbl. I was thinking about removing any dates 1st(before appending) that fall on a weekend or holiday to reduce the number of entries in the master tbl. Not sure how to do that....
Plus include your method of counting work days minus the holidays and leave days....

 

[sara82]

Thank you again Jon K

I replaced it with DCount as follows:

NumberOfWorkDays: [Date Approved]-[Date Received]-(DateDiff("ww",[Date Received],[Date Approved],7)-(Weekday([Date Received])=7))-(DateDiff("ww",[Date Received],[Date Approved],1)-(Weekday([Date Received])=1))-DCount("*","[tblHolidays]","[HolidayDate] between DateValue('" & [Date Received] & "') and DateValue('" & [Date Approved] & "')")+1

Now when I run the query wherever Date Approved doesn't have a date it has #Error.
When I try to create the report it tells me "Date Type mismatch in criteria expression " because some records for the Date Approved field are empty.

 

[Jon K]

To test for null dates, you can use the IIF() function with IsNull():-

IIF(IsNull([Date Approved]) or IsNull([Date Received]), Null, [Date Approved] - [Date Received] - (DateDiff("ww", [Date Received], [Date Approved], 7) - (Weekday([Date Received]) = 7)) - (DateDiff("ww", [Date Received], [Date Approved], 1) - (Weekday([Date Received]) = 1)) - DCount("*","[tblHolidays]","[HolidayDate] between DateValue('" & [Date Received] & "') and DateValue('" & [Date Approved] & "')")) + 1


In the database, I have changed the date field names to the same as yours, deleted some dates and built a new query with the above expression. The query now runs without displaying #ERROR.


But I can't reproduce the data type mismatch error when I build a report based on the query, grouped by Category and sorted by NumberOfWorkingDays. The report runs fine.
.

 

[sara82]

Thank you Jon K. You've been a tremendous help. I really appreciate it. My reports are running great now.

 

[a2ari]

I tried running the database that you have zipped there, but I get:
"Unrecognized database format"

any ideas?

Thanks

 

[Jon K]

Try this Access 97 version.

 

[a2ari]

I'm trying to turn this into a "How long did this take to finnish", as oppose to, "How many days left to work". As such, I need to calculate how many hours went by, not including holiday or weekend, not just days.
I suppose I could mutiply by 24, but how do I get fraction of days?
Thoughts?

Thanks

 

[a2ari]

I figured it out
I don't add the +1 at the end, btw, so as not to count the first day as a whole day.

Essentially I take all that, I multiply it by 24 and then add (Hour([Date Approved]) - Hour([Date Recieved]))

simple enough, I've just been working on other things till now.

 

[SarahO]

To test for null dates, you can use the IIF() function with IsNull():-

IIF(IsNull([Date Approved]) or IsNull([Date Received]), Null, [Date Approved] - [Date Received] - (DateDiff("ww", [Date Received], [Date Approved], 7) - (Weekday([Date Received]) = 7)) - (DateDiff("ww", [Date Received], [Date Approved], 1) - (Weekday([Date Received]) = 1)) - DCount("*","[tblHolidays]","[HolidayDate] between DateValue('" & [Date Received] & "') and DateValue('" & [Date Approved] & "')")) + 1


In the database, I have changed the date field names to the same as yours, deleted some dates and built a new query with the above expression. The query now runs without displaying #ERROR.


But I can't reproduce the data type mismatch error when I build a report based on the query, grouped by Category and sorted by NumberOfWorkingDays. The report runs fine.
.

Jon,
You were helping another programmer with this problem back in Jan 2005. I have found this really useful and wondered if you would be able to help me a bit further with this please. I have used your SQL above to calculate the working days including the "DateApproved" and "DateReceived" and exclude weekends and holidays. However how do you modify this script so it will work out the difference between the "DateApproved" and "DateReceived" excluding the holidays and weekends?
E.g.
If the Date Received = 23/12/2006
The Date Approved = 29/12/2006
24 & 25/12/2006 = Weekend
26 & 27/12/2006 = Holidays, in the tblHolidays table
The above script returns "3" in the NoOfWorkingDays
I don't want it to include the "DateReceived" so it will return "2" in the NoOfWorkingDays.

Any help with this would be gratefully received.
Thanks
Sarah

 

[Bat17]

[Date Received] & "') and DateValue('" & [Date Approved] & "')")) + 1

at a quick look I would say taking the '+1' off of the end of the equation would reduce you count by the 1 required

Peter