Converting and Calculating Dates on a Form

[FA]

Hi Freinds,

I have a TextBox- txtNumDays which is associated with TestStartDate and
TestEndDate. If you put Number of Days and TestStartDate, it calculates
the TestEndDate. I also have VacationBeginDate and VacationEndDate on a
different form and different table, I want the txtNumDays to calculate
the TestEndDate by Subtracting the difference of VacationBeginDate and
VacationEndDate.

I am using the following module to calculate the TestEndDate:
Public Function MyDateAdd(dtStart As Date, intDays As Integer) As Date


Dim dtEnd As Date
Dim i As Integer


i = 0
dtEnd = dtStart


If intDays < 1 Then
Exit Function
End If


Do While i < intDays


dtEnd = dtEnd + 1
Select Case Weekday(dtEnd)
Case 2 To 6
' regular week day.MON - Fri..count one day
i = i + 1
End Select
Loop


MyDateAdd = dtEnd


End Function


Also in AfterUpdate EVent of txtNumDays i have

me.txtEndDate = MyDateAdd(me.txtStartDate,me.txtNumDays)


If possible please solve this issue, i would really really appreciate
it.

 

[David C. Holley]

Tell me if I've got the basic formulas wrong

TestEndDate = (VactionEndDate - VacationBeginDate) + TestStartDate

Secondary to that, you'll want to use DateDiff() and DateAdd() to
actually perform the calculatons as they're designed specifically to
work with dates. If you need the TestEndDate to fall on a weekday, you
can use DatePart() to test if the date falls on a Sat or Sun and if so
add 1 or 2 (using DateAdd()) to the inital TestEndDate to shift it.

 

[Klatuu]

Below are two functions. CalcWorkDays returns the number of working days
between two dates. AddWorkDays returns a date that is a number of working
days from a date. Both exclude Saturdays and Sundays. Both include
reference to a table named Holidays which has a date field named holdate.
Both functions also exclude any dates found in that table that are within the
range of dates being examined.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

************************************
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = DateAdd("d", intAdd, dtmReturnDate)
End Function

 

[FA]

I did the following changes in my MyDateAdd Function
i am passing VacationStartDate, and VacationEndDate but its not giving
me the correct TestEndDate
Public Function MyDateAdd(dtStart As Date, intDays As Integer,
dtVacationStartDate As Date, dtVacationEndDate As Date) As Date


Dim dtEnd As Date
Dim I As Integer
Dim dtMiddle1 As Date
Dim dtMiddle2 As Date

I = 0
dtEnd = dtStart
dtMiddle1 = dtVacationStartDate
dtMiddle2 = dtVacationEndDate


If intDays < 1 Then
Exit Function
End If


Do While I < intDays


dtEnd = dtEnd - (dtMiddle2-dtMiddle1) + 1
Select Case Weekday(dtEnd)
Case 2 To 6
' regular week day.MON - Fri..count one day
I = I + 1
End Select
Loop


MyDateAdd = dtEnd


End Function