Calculate number of days in months given a multi-month time period

  • Thread starter Thread starter PF
  • Start date Start date
P

PF

I have data for billing cycles that span multiple calendar months. Sometimes
the billing cycle is only one month (i.e., 10/17/09 - 11/16/09), sometimes
two or more months (ie., 10/17/2009 - 12/15/2009)

I am trying to calculate the number of days that occur in each month during
that time period. In the first example, I am looking for access to return 15
days for October 2009 and 16 days in November 2009. In the second example, 15
days in October, 30 days in November, and 15 days in December.

Is there a way to do this in access? Presently using 2003 but hoping we
soon get 07.
 
DateDiff() should be what you are looking for.

DateDiff("d",#10/17/09#,#11/16/09#) + 1 = 31
DateDiff("d",#10/17/2009#,#12/15/2009#) + 1 = 60

For more information look at the help on the subject. It can be used in
VBA, queries, forms, reports...
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Thanks.
But how do i calculate how many of those days occurred in October, how many
in November, and how many in December?
 
In this specific case you'd need to create a custom function. Below is one I
threw together rapidly to try to help you out. It is not the most elegant
way to do this but it does work. Feel free to improve upon it.

Function NoDaysByMonth(dtStart As Date, dtEnd As Date) As String
On Error GoTo Error_Handler
Dim iNoMonths As Integer
Dim dtFirstofMonth As Date
Dim dtLastOfMonth As Date

iNoMonths = DateDiff("m", dtStart, dtEnd) 'No months between the date

'Dates are in the same month
If iNoMonths = 0 Then
NoDaysByMonth = DateDiff("d", dtStart, dtEnd) + 1 & " days for " &
Format(dtStart, "mmmm, yyyy")
Exit Function
End If

'Dates are in different months
NoDaysByMonth = DateDiff("d", dtStart, DateAdd("m", 1,
DateSerial(Year(dtStart), _
Month(dtStart), 1)) - 1) + 1 & " days for " &
Format(dtStart, "mmmm, yyyy")

For i = 1 To iNoMonths - 1
dtFirstofMonth = DateAdd("m", i, dtStart)
dtFirstofMonth = DateSerial(Year(dtFirstofMonth),
Month(dtFirstofMonth), 1)

dtLastOfMonth = DateAdd("m", i, dtStart)
dtLastOfMonth = DateAdd("m", 1, DateSerial(Year(dtLastOfMonth),
Month(dtLastOfMonth), 1)) - 1

NoDaysByMonth = NoDaysByMonth & vbCrLf & _
DateDiff("d", dtFirstofMonth, dtLastOfMonth) + 1 & "
days for " & _
Format(dtLastOfMonth, "mmmm, yyyy")
Next i

NoDaysByMonth = NoDaysByMonth & vbCrLf & DateDiff("d",
DateSerial(Year(dtEnd), _
Month(dtEnd), 1), dtEnd) + 1 & " days for " &
Format(dtEnd, "mmmm, yyyy")


Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: NoDaysByMonth" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Thanks - I'm very much a VB novice - but I think i get the gist and think
this will help me a lot.
 
Another way to get at this would be to create a query.

1. To do so, you would need a table (tbl_Numbers) with one field
(intNumber) with 10 records (values from 0 to 9). I use this table for lots
of different purposes, but generating date sequences is the most frequent.

2. Then, you need a query (qry_Numbers_to_99) which generates a sequence of
numbers from 0 to some other value (in your case you probably only need < 100
days in your result set). It would look like:

SELECT Tens.IntNumber * 10 + Ones.intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

3. Finally, you need the query (qry_Billing_Days) that returns the billing
year, month, and the number of days in each of those months.

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT Year(DateAdd("d",[intNumber],[StartDate])) AS BillYear,
Month(DateAdd("d",[intNumber],[StartDate])) AS BillMonth,
Count(IntNumber) AS Days
FROM qry_Numbers_to_99
WHERE (((DateAdd("d",[intNumber],[StartDate]))<=[EndDate]))
GROUP BY Year(DateAdd("d",[intNumber],[StartDate])),
Month(DateAdd("d",[intNumber],[StartDate]));
 
Back
Top