printmedia

asked on

# Calculate Working Days between two dates

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!

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",

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!

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
There isn't a StartDate?

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;-)

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

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
```

It could be done this way.

/gustav

/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
```

ASKER

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!

scgstuff, I changed the function a bit:

Do While DateCnt > EndDate

If Not IsNull(DLookup("HoliDate",

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!

Shawn