Date and Day Problems In Access

[confusionguy]

Hi, can anyone help me with dates and day with Access.

Currently i have a field named "Aging" and is calculated as the difference between Today's Date and the Date that the loan is due. Both fields have the Medium Date Format.

However, the true aging is not reflected as weekends (Sat & Sun) are taken in the calculation.

Are there any ways that I can use to reflect that Aging is calculated based on the business working days, ie. Mon to Friday??

Appreciate any assistance and advice in this. Thanks in advance!

[xordevoreaux]

Networking = net (not gross) number of working days, not "networking" days.

Not only removes saturday and sunday, but references my "tblHoliday" list of my company's sanctioned days off (which is important for me but you can remove the functionality for accounting for holidays falling on a workday).

Tblholiday is just a table consisting of a single date field, nothing else.

--------------------

Public Function NetWorkingDays(sStartDate, sEndDate) As Long
'Returns the number of weekdays between two given dates, then subtracts from that number the
'count of any user-defined holidays from tblHoliday that fall within and including the given dates.

Dim iDays As Long
Dim iWorkDays As Long
Dim sDay As Long
Dim i As Long

Dim rst As ADODB.Recordset

If IsNull(sStartDate) = True Or IsNull(sEndDate) = True Then
NetWorkingDays = 0
Exit Function
End If

iDays = DateDiff("d", sStartDate, sEndDate)

iWorkDays = 0
NetWorkingDays = 0

For i = 0 To iDays
'First day of the week is sunday
sDay = Weekday(DateAdd("d", i, sStartDate))
If sDay <> 1 And sDay <> 7 Then
iWorkDays = iWorkDays + 1
End If
DoEvents
Next

If iWorkDays = 0 Then
Exit Function

Else:

Set rst = New ADODB.Recordset
rst.Open "tblHoliday", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
DoEvents
If CDate(rst!DateHoliday) >= CDate(sStartDate) And _
CDate(rst!DateHoliday) <= CDate(sEndDate) Then
sDay = Weekday(CDate(rst!DateHoliday), vbSunday)
If sDay <> 1 And sDay <> 7 Then iWorkDays = iWorkDays - 1
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
End If

[confusionguy]

I still dun understand the usage of the Networking Days.

Does it means I have to specify the Start Date and End Date everyweek for the formula to work?

Mine is a simple system and I do not wish to use lengthy coding. I am not adept at coding.

As such, how do I define the Start Date and End Date indefinitely so I can rest my mind that the formula will work and is based on 5 working days per week.

Able to demonstrate it in SQL Language?

Thank you so much.

[xordevoreaux]

Select * from tblWhatever where NetWorkingDays([MyStartDate], [MyEndDate]) > 5