Date difference problem

[Susan]

If I haven't posted this in the correct group, please let
me know...I need to calculate the number of days that a
client has been enrolled in our program in a given month.
The problem is that StartDate and EndDate may be in
different months. For example:

StartDate EndDate Days
June 28 July 12 12 (from July 1 to July 12)
July 4 July 12 9
July 10 August 22 (from July 10 to July 31)

The easy part is example # 2. It's the other two that are
giving me fits! Is there any help out there? The
resulting total would be shown on a form for each person.

 

[fredg]

If I haven't posted this in the correct group, please let
me know...I need to calculate the number of days that a
client has been enrolled in our program in a given month.
The problem is that StartDate and EndDate may be in
different months. For example:

StartDate EndDate Days
June 28 July 12 12 (from July 1 to July 12)
July 4 July 12 9
July 10 August 22 (from July 10 to July 31)

The easy part is example # 2. It's the other two that are
giving me fits! Is there any help out there? The
resulting total would be shown on a form for each person.

Aren't you storing the StartDate and EndDate fields as Date datatypes?

=DateDiff("d",[StartDate],[EndDate])
or..
= [EndDate] - [StartDate]

 

[Graham Mandeno]

Hi Susan

I think the easiest way to do this is to write your own function:

Function EnrolledDaysInMonth( ByVal StartDate as Variant, _
ByVal EndDate as Variant, DateInMonth as Date) as Integer
Dim tmpDate as Date
If Not (Isdate(StartDate) and IsDate(EndDate) then Exit Function
' calculate the date of the first day in the month
tmpDate = DateSerial(Year(DateInMonth), Month(DateInMonth), 1)
' if Enddate is before this month then return zero
If EndDate < tmpDate then Exit Function
' if StartDate is before this month then start from the 1st of the month
If StartDate < tmpDate then StartDate = tmpDate
' calculate the date of the last day in the month
tmpDate = DateSerial(Year(DateInMonth), Month(DateInMonth)+1, 0)
' if StartDate is after this month then return zero
If StartDate > tmpDate then Exit Function
' if EndDate is after this month then end on the last of the month
If EndDate > tmpDate then EndDate = tmpDate
' now do the calculation
EnrolledDaysInMonth = EndDate - StartDate + 1
End Function

DateInMonth can be any date in the month you want to calculate in.

 

[Susan]

Thanks, Graham. It looks a little complicated, but I'll
give it a try. I appreciate your response.

Susan

-----Original Message-----
Hi Susan

I think the easiest way to do this is to write your own function:

Function EnrolledDaysInMonth( ByVal StartDate as Variant, _
ByVal EndDate as Variant, DateInMonth as Date) as Integer
Dim tmpDate as Date
If Not (Isdate(StartDate) and IsDate(EndDate) then Exit Function
' calculate the date of the first day in the month
tmpDate = DateSerial(Year(DateInMonth), Month (DateInMonth), 1)
' if Enddate is before this month then return zero
If EndDate < tmpDate then Exit Function
' if StartDate is before this month then start from the 1st of the month
If StartDate < tmpDate then StartDate = tmpDate
' calculate the date of the last day in the month
tmpDate = DateSerial(Year(DateInMonth), Month(DateInMonth) +1, 0)
' if StartDate is after this month then return zero
If StartDate > tmpDate then Exit Function
' if EndDate is after this month then end on the last of the month
If EndDate > tmpDate then EndDate = tmpDate
' now do the calculation
EnrolledDaysInMonth = EndDate - StartDate + 1
End Function

DateInMonth can be any date in the month you want to calculate in.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

If I haven't posted this in the correct group, please let
me know...I need to calculate the number of days that a
client has been enrolled in our program in a given month.
The problem is that StartDate and EndDate may be in
different months. For example:

StartDate EndDate Days
June 28 July 12 12 (from July 1 to July 12)
July 4 July 12 9
July 10 August 22 (from July 10 to July 31)

The easy part is example # 2. It's the other two that are
giving me fits! Is there any help out there? The
resulting total would be shown on a form for each

person.


.