Working Days

[Guest]

Hi,

Please help.... I am trying to workout the number of Working Days between
the Visit Date and a Close Job Date. How can I do this, I know in excel its
NetworkDays(A1,B1) how is this possible in Access?

Also once I have worked out the number of days between these dates how can I
group all the number of days, for example... All days between 0&6 is in week,
all days between 7&13 is week2 and days between 14&20 is week3?

Thanks,
Jez

 

[Cinzia]

Hi,

Please help.... I am trying to workout the number of Working Days between
the Visit Date and a Close Job Date. How can I do this, I know in excel its
NetworkDays(A1,B1) how is this possible in Access?

Also once I have worked out the number of days between these dates how can I
group all the number of days, for example... All days between 0&6 is in week,
all days between 7&13 is week2 and days between 14&20 is week3?

Thanks,
Jez

hi jez,
try those functions

Function DateSpan(BegDate As Date, EndDate As Date) As Variant
'FROM http://support.microsoft.com/kb/213182/en-us
'return an Array with all the days between BegDate and EndDate

Dim DateArray() As Variant, i As Integer, Span As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
Span = EndDate - BegDate + 1
ReDim DateArray(1 To Span)
For i = 1 To Span
DateArray(i) = BegDate + i - 1
Next
DateSpan = DateArray

End Function

'Return the number of working days between BeginDate and EndDate
'excluding sunday and saturday and the Dates included in Holidays Array
'it should work Like Excel Function NetworkDays
Function WorkingDays(BeginDate As Date, EndDate As Date, Optional Holidays
As Variant = Null) As Integer

Dim Days As Integer
Dim nonWorkingDays As Integer
Dim i As Integer
Dim dayOfWeek As Integer
If EndDate < BeginDate Then 'case endDate < Begindate not considered
WorkingDays = -1
Exit Function
End If
Days = DateDiff("d", BeginDate, EndDate) + 1
nonWorkingDays = 0
For i = 0 To Days - 1
dayOfWeek = Weekday((BeginDate + i), vbSunday)
If dayOfWeek = vbSaturday Or dayOfWeek = vbSunday Then
nonWorkingDays = nonWorkingDays + 1
End If
Next i
If IsNull(Holidays) Then

WorkingDays = Days - nonWorkingDays

Else
Dim curDay As Date

For i = 1 To UBound(Holidays)
curDay = Holidays(i)
dayOfWeek = Weekday(curDay, vbSunday)
If curDay >= BeginDate And curDay <= EndDate And (dayOfWeek <>
vbSaturday And dayOfWeek <> vbSunday) Then
nonWorkingDays = nonWorkingDays + 1
End If
Next i

WorkingDays = Days - nonWorkingDays

End If
End Function

Ho to use:
Dim myHolidays(1 to 3) as date

myHolidays(1) =Cdate("2006/12/31")
myHolidays(2) =Cdate("2007/1/31")
myHolidays(3) =Cdate("2007/04/25")
Debug.print WorkingDays(Cdate("2006/12/30"), Cdate("2007/06/30"),
myHolidays)
or using DateSpan

Debug.print WorkingDays(Cdate("2006/12/30"), Cdate("2007/06/30"),
DateSpan(Cdate("2007/02/20"), Cdate("2007/03/05")))

Try it and give me a feedback, if you can.


To group by week see the function
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

Bye

 

[Guest]

Jez,
I've encountered much the same problem myself in the past and unfortunately
the only ways I've ever encountered to solve it have given an access VBA
solution. Now I don't mind that, but when I'm asked as a developer to provide
a formula to stick into a how-to guide for other non-developers to be able to
do it, this just isn't satisfactory.

I've therefore come up with a formula that works (at least in Access 2003)
and can be used in an access query. It looks complicated, but all you have to
do is to replace the [start date] and [completed date] entries with whatever
is appropriate to your database.

DaysWorked : CLng(Mid(Switch(Weekday([start
date])=1,"0123455",Weekday([start date])=2,"0123444",Weekday([start
date])=3,"0123334",Weekday([start date])=4,"0122234",Weekday([start
date])=5,"0111234",Weekday([start date])=6,"0001234",Weekday([start
date])=7,"0012345"),(DateDiff("d",[start date],[completed
date])-(Int(DateDiff("d",[start date],[completed
date])/7)*7))+1,1))+(Int((DateDiff("d",[start date],[completed date]))/7)*5)

Basically, what it does is:
1) works out the number of whole weeks
2) works out the number of days remaining that don't make up a whole week
(i.e. 0 to 6)
3) works out the day of the week of the start date, and uses this to compare
against a lookup giving you the number of working days as compared against
total days from there
4) adds the two together.

It might be less efficient than a lot of other ways (e.g. automatically
populating a "days taken" field once a record is marked as complete) as the
results will need to be recalculated each time, but it IS a way of doing it
just using the queries in MS Access, which sometimes you may need to do.