Solved

Calculate Working Days between two dates

Posted on 2008-06-11
7
1,984 Views
Last Modified: 2009-05-11
Hi all.

I'm working on an Access 2003 query that will display how many working days it took to complete an order.

There are 2 dates: PromiseDate (or BegDate) and CompletionDate (or EndDate)

I also have a table called Holidays, which holds holiday dates (i.e. 12/25/2008, 01/01/2008) so they do not get included in the calculation and therefore not count against the length of time it took to complete an order...so it simply calculates the working days. I've been using the following function:
***************************************************************************************************************************
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
   
    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer
   
    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    DateCnt = BegDate
    EndDays = 0
       Do While DateCnt < EndDate
       
        If Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & DateCnt & "#")) Then
            EndDays = EndDays - 1
        End If
       
        If Format(DateCnt, "ddd") <> "Sun" And _
            Format(DateCnt, "ddd") <> "Sat" Then
            EndDays = EndDays + 1
        End If
     
   
      DateCnt = DateAdd("d", 1, DateCnt)
   
      Loop
     
      Work_Days = EndDays

End Function
*******************************************************************************************************************************
But as you can see, the loop condition is while the PromiseDate is before the CompletionDate, there may be times when we complete an order before the promise date.

In other words, let's say we promised to complete an order on 10/15/2008, but we actually complete the order on 10/14/2008. Then the calculated work days it took to complete it was -1.

Currently the query will display 0.

There are two parts where I'm having difficulty:

1. The loop condition (what if the promise date is larger than the completion date, as in the example above)
2. DateCnt = DateAdd("d", 1, DateCnt) --> Using the above example, it should subtract a day instead of add one, how do I do that?

Thank you in advance!


0
Comment
Question by:printmedia
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Accepted Solution

by:
scgstuff earned 500 total points
ID: 21764474
Try this:


Shawn

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   

    Dim WholeWeeks As Variant

    Dim DateCnt As Variant

    Dim EndDays As Integer

    

    BegDate = DateValue(BegDate)

    EndDate = DateValue(EndDate)

    DateCnt = BegDate

    EndDays = 0

       If DateCnt < EndDate Then

       Do While DateCnt < EndDate

        

        If Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & DateCnt & "#")) Then

            EndDays = EndDays - 1

        End If

        

        If Format(DateCnt, "ddd") <> "Sun" And _

            Format(DateCnt, "ddd") <> "Sat" Then

            EndDays = EndDays + 1

        End If

      

   

      DateCnt = DateAdd("d", 1, DateCnt)

   

      Loop
 

      Else
 

      Do While DateCnt > EndDate

        

        If Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & DateCnt & "#")) Then

            EndDays = EndDays - 1

        End If

        

        If Format(DateCnt, "ddd") <> "Sun" And _

            Format(DateCnt, "ddd") <> "Sat" Then

            EndDays = EndDays + 1

        End If

      

   

      DateCnt = DateAdd("d", -1, DateCnt)

   

      Loop
 

      End If

      

      Work_Days = EndDays
 

End Function

Open in new window

0
 
LVL 5

Expert Comment

by:scgstuff
ID: 21764484
This will allow thie function to subtract if it is a larger promise date.  The reason it was returning 0 is you set it to 0 and then only allowed the loop if the promise date was smaller, so it never actually executed that part of the loop.

Shawn
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21765486
There isn't a StartDate?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
ID: 21765503
With a StartDate, EndDate, and CompletionDate, do you also want to know the status of those projects which are underway, or only the status of those projects which are completed - in which case the EndDate is irrelevant?  What I call adding smoke;-)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21766992
Here's the function we use. It has the advantage that it accepts reversed input dates and it works in an international environment. Also, should you need it, it is much faster for large periods.

To exclude the holidays, simply look up these for the period in question, count those that fall on workdays and deduct that count. This is much faster than looking up each and every day:

lngWorkdaysOfHolidays = DCount("*", "[Holidays]", "[Holidate] Between #" & BegDate & "# And #" & EndDate & "# And Weekday([Holidate], 2) < 6")

To be absolutely precise you may need to exclude those situations where both BegDate and EndDate fall and the same holiday as this would return a negative total of workdays, or you could perform the counting only for a positive count of workdays:

lngWorkdays = ISO_WorkdayDiff(BegDate, EndDate)
If lngWorkdays > 0 Then
  lngWorkdays = lngWorkdays - lngWorkdaysOfHolidays
End If

/gustav
Public Function ISO_WorkdayDiff( _

  ByVal datDateFrom As Date, _

  ByVal datDateTo As Date) _

  As Long
 

' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.

' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.

' Limitation: Does not count for public holidays.

' May be freely used and distributed.

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen

' 2000-10-03. Constants added.

'             Option for 5 or 6 working days per week added.
 

  Const cbytWorkdaysOfWeek  As Byte = 5
 

  Dim bytSunday             As Byte

  Dim intWeekdayDateFrom    As Integer

  Dim intWeekdayDateTo      As Integer

  Dim lngDays               As Long

  Dim datDateTemp           As Date

  

  ' Reverse dates if these have been input reversed.

  If datDateFrom > datDateTo Then

    datDateTemp = datDateFrom

    datDateFrom = datDateTo

    datDateTo = datDateTemp

  End If

  

  ' Find ISO weekday for Sunday.

  bytSunday = WeekDay(vbSunday, vbMonday)

  

  ' Find weekdays for the dates.

  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)

  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)

  

  ' Compensate weekdays' value for non-working days (weekends).

  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)

  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)

  

  ' Calculate number of working days between the two weekdays, ignoring number of weeks.

  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))

  ' Add number of working days between the weeks of the two dates.

  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))

  

  ISO_WorkdayDiff = lngDays
 

End Function

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21767207
It could be done this way.

/gustav
Public Function ISO_WorkdayDiff( _

  ByVal datDateFrom As Date, _

  ByVal datDateTo As Date, _

  Optional ByVal booExcludeHolidays As Boolean) _

  As Long
 

' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.

' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.

' May be freely used and distributed.
 

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen

' 2000-10-03. Constants added.

'             Option for 5 or 6 working days per week added.

' 2008-06-12. Option to exclude holidays from the count of workdays.
 

  Const cbytWorkdaysOfWeek  As Byte = 5

  ' Name of table with holidays.

  Const cstrTableHoliday    As String = "tblHoliday"

  ' Name of date field in holiday table.

  Const cstrFieldHoliday    As String = "HolidayDate"
 

  Dim bytSunday             As Byte

  Dim intWeekdayDateFrom    As Integer

  Dim intWeekdayDateTo      As Integer

  Dim lngDays               As Long

  Dim datDateTemp           As Date

  Dim strDateFrom           As String

  Dim strDateTo             As String

  Dim lngHolidays           As Long

  Dim strFilter             As String

  

  ' Reverse dates if these have been input reversed.

  If datDateFrom > datDateTo Then

    datDateTemp = datDateFrom

    datDateFrom = datDateTo

    datDateTo = datDateTemp

  End If

  

  ' Find ISO weekday for Sunday.

  bytSunday = WeekDay(vbSunday, vbMonday)

  

  ' Find weekdays for the dates.

  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)

  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)

  

  ' Compensate weekdays' value for non-working days (weekends).

  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)

  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)

  

  ' Calculate number of working days between the two weekdays, ignoring number of weeks.

  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))

  ' Add number of working days between the weeks of the two dates.

  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))

  

  If booExcludeHolidays And lngDays > 0 Then

    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")

    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")

    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""

    lngHolidays = DCount("*", cstrTableHoliday, strFilter)

  End If

  

  ISO_WorkdayDiff = lngDays - lngHolidays
 

End Function

Open in new window

0
 

Author Comment

by:printmedia
ID: 21771715
Thanks for all your help, but I went with scgstuff's answer since it's the same function we've used until now.

scgstuff, I changed the function a bit:

Do While DateCnt > EndDate
       
        If Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" & DateCnt & "#")) Then
            EndDays = EndDays + 1
        End If
       
        If Format(DateCnt, "ddd") <> "Sun" And _
            Format(DateCnt, "ddd") <> "Sat" Then
            EndDays = EndDays - 1
        End If
     
   
      DateCnt = DateAdd("d", -1, DateCnt)
   
      Loop

******************************************************************************************************************************
So that way if the calculated days is < 0, when know it was completed before the scheduled promise date.

Thanks again!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now