Link to home
Start Free TrialLog in
Avatar of MaeMcG
MaeMcG

asked on

Date Difference - only including business days? How to run a query exactly (step by step)

I am trying to run a date difference query in my Access database. I am new to Access so I am not even sure what the query would be and how exactly I would run it.

I have two fields named Date of Discovery and Date Processed and I need to find the date difference between the two; or how many days are in-between the two. However, I also need it to be only business days  so it does not include weekends when counting, is there anyway to do that? I would like this number difference to appear in my field named # of Days.

What exactly would my query be and where would I save this or how do I run it? The name of my Table is EF&L Table.

THANK YOU!
Avatar of nutsch
nutsch
Flag of United States of America image

You should add / replace the zone for your question to the MS Access zone. This is the excel side of town (you might still get the right answer, but it could take longer)

Thomas
I don't think you can't accomplish this with just a query, you're going to need to write some code in order to handle the weekends/holidays.

If you're not looking for Holidays then you might check out this article:
http://office.microsoft.com/en-us/access/HA010550781033.aspx

If you do need holidays then you might look at this article as well:
http://www.tek-tips.com/viewthread.cfm?qid=719764&page=2
Avatar of whitbacon
whitbacon

This function pasted in a query column should come close.  Does not accomodate holidays.

datediff(d,[Date of Discovery],[Date Processed]) - (datediff(ww,[Date of Discovery],[Date Processed]) * 2)
Avatar of Jeffrey Coachman
<No Points wanted>

What Torrwin and whitbacon posted may be as close as you can get without going crazy with Custom Functions or code.
(Again, this is even before you can factor in Holidays!)
:-O

Please remenber that DateDiff, means just that, ...the difference in *dates*, ...not "Working" Dates.

JeffCoachman
Avatar of MaeMcG

ASKER


Public Function FixedHoliday(intYear As Integer, intMonth As Integer, intDay As Integer) As Date

    FixedHoliday = DateSerial(intYear, intMonth, intDay)

End Function

Public Function FloatingHoliday(intYear As Integer, intMonth As Integer, intWeek As Integer, _
    intDayOfWeek As Integer) As Date

    FloatingHoliday = DateSerial(intYear, intMonth, (8 - WeekDay(DateSerial(intYear, intMonth, 1), _
        (intDayOfWeek + 1) Mod 8)) + ((intWeek - 1) * 7))

End Function

Public Function WeekDaysPerMonth(intYear As Integer, intMonth As Integer, _
    intDayOfWeek As Integer) As Integer

    Dim i As Integer
    Dim intTotalDays As Integer
    Dim intWeekDayCount As Integer
   
    intWeekDayCount = 0
    intTotalDays = Day(DateAdd("d", -1, DateSerial(intYear, intMonth + 1, 1)))
   
    For i = 1 To intTotalDays
        If WeekDay(DateSerial(intYear, intMonth, i)) = intDayOfWeek Then
            intWeekDayCount = intWeekDayCount + 1
        End If
    Next i
   
    WeekDaysPerMonth = intWeekDayCount
   
End Function

Public Function EasterDate(intYear As Integer) As Date

    Dim i As Integer
   
    i = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
   
    EasterDate = DateSerial(intYear, 3, 1) + i + (i > 48) + 6 - _
        ((intYear + intYear \ 4 + i + (i > 48) + 1) Mod 7)

End Function



This is the code provided inTorrwin's post.

I am a beginner at Access so I will need a step by step walk through if someone could please provide it - thank you!!!
Does the code give you the result you are looking for?
Avatar of MaeMcG

ASKER

I don't know how to run that code... it is extremely complicated, so I haven't run it yet. Where would I put it -- could you help tell me what to do? Many thanks!
I had hoped to have some time to get you started with this last night.  But this might help.

Forget about the code for right now.  Basically, that code is for if you don't want to manually update tables which contain holiday data.   A good starting point for you would be to create a table for holidays that has to be manually updated.  Once you are comfortable using this table, then you can use the above code to populate it automatically.  So, to start, I would create a table with two fields: Holiday_Name and Holiday_Date.  Then I would manually enter all of the holidays.

Do you have any forms in your Access database yet, or are you only working with the query right now?
Assuming you have your Weekend Query and Holiday table set up you can create a query that combines them.  Create a subquery that gets the count of Holidays between your start and end dates and subtract this count from your Weekend Query.

An example of your subquery might be:

SELECT Count(Holiday_Date)
FROM Holidays
WHERE Holiday_Date BETWEEN Date_of_Discovery AND Date_Processed;

If this doesn't make sense just let me know.
Did this solve your problem?  This has been inactive for some time now.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial