Sorry, Chris, I didn't see the part about wanting a date. Here is what you
really need. There are two function, so you need both. Put them both in a
standard module.
You don't need to change anything.
All you need to do is call it with DateLoggedIn
=AddWorkDays(DateLoggedIn, 3)
'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/9/2006 11:50
' Author : Dave Hargis
' Purpose : Returns a date that is a number of work days from the original
date
' : OriginalDAte is the date from which to calculate
' : DaysToAdd is the number of days to add or subtract from
OriginalDate
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
Dim dtmCheckDay As Date 'Keeps track of the date to be tested
Dim lngDayCount As Long 'Counts workdays
Dim lngAddDays As Long 'Counts all days to calculate the return date
Dim lngAdder As Long 'Either +1 or -1 / used to add or subtract a day.
On Error GoTo AddWorkDays_Error
'Set up for negative or postive adding
If DaysToAdd < 0 Then
lngAdder = -1
ElseIf DaysToAdd > 0 Then
lngAdder = 1
Else
lngAdder = 0
End If
dtmCheckDay = OriginalDate
'Icrement through the calender
Do Until lngDayCount = DaysToAdd
If IsWorkDay(dtmCheckDay) Then
'Add +/- 1 to the number of workdays so far
lngDayCount = lngDayCount + lngAdder
End If
'Increment the calendar day
dtmCheckDay = DateAdd("d", lngAdder, dtmCheckDay)
'Add +/- 1 to the actual days
lngAddDays = lngAddDays + lngAdder
Loop
AddWorkDays = DateAdd("d", lngAddDays - lngAdder, OriginalDate)
AddWorkDays_Exit:
On Error GoTo 0
Exit Function
AddWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : IsWorkDay
' DateTime : 4/14/2004 10:55
' Author : Dave Hargis
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
On Error GoTo IsWorkDay_Error
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
IsWorkDay_Exit:
On Error GoTo 0
Exit Function
IsWorkDay_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure IsWorkDay of Module modDateFunctions"
GoTo IsWorkDay_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
Chris said:
Thank you for your help Klatuu,
I have put in the module. But I am still stuck, my knowledge of vba is
limited.
Do I need to change "dtmStart" for my field name "DateLoggedIn", and do I
have to create a new field called "dtmEnd"? it might sound stupid, but I am
lost and yet I feel I am not far.
When applied your module, it came out with numbers of days, which is fine,
but I need to see this as a date, not a digit:
Ie DateloggedIn: 14/09/07
Result of function should give me a new date which would be 3 days max from
the DateLoggedIn, excluding Sat and Sundays and holidays.
The result should be:
Duedate: 19/09/07.
Hope this is not too confusing, but the whole is driving me mad!
DueDate:
Klatuu said:
Here is a simple function that will do this for you.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
Hi,
I have a field: DateLoggedIn, which stores the date when data is entered,
What I am looking for is a function, and how to apply this function, i.e. in
a query in order to calculate an expression a DueDate which will display a
new date, 3 working days from the DateLoggedIn excluding weekends and
holidays.
I have already a TblHolidays that contains all holidays for the next 12
months.
Example:
DateLoggedIn: 13-Sep-07
DueDate: Should show 17-Sep-07, not 16-Sep-07 as it is a Sunday.
Much help greatly appreciated.
Chris1
