Find # months in specific year given FDOC and LDOC 1

[joel009]

This should be easy but I'm finding it's not.

I have a report that I need to populate the annual charge and I have the FDOC and LDOC. How do I find the number of months to charge for a given year?

I have been looking at the DateDiff and it gives me the number of months from the FDOC or LDOC, both positive values.

I feel I'm missing something simple.

Joel

 

[Remou]

Can you run that by me again with a little more detail, please?

http://lessthandot.com

 

[joel009]

I have a table for a report. Each row has 2 dates. FDOC is the first date of charge. LDOC is the Last date of charge. All FDOC start on the 1st of the month. All LDOC end on the last day of the month. The usual term between the FDOC and the LDOC is 36 months but not always. Each row has a monthly billing amount. I need to report out the annual charge by year even if it is zero from 2006 - 2014. So if the first date of charge is 03/1/2006 and the LDOC is 4/30/2009 the charge for 2006 would only be 10 months. The charge for 2009 would be 4 months. I also need the values for 2007,2008. I have been looking at the DateDiff,Date Serial function and can't make it work.
I am looking at a function where I would send it the FDOC, LDOC and year returningthe number of month which I could apply to the monthly amount. Plugging away but I thought someone may have an easier way.
My code is very sketchy. Nothing close enough to save.

 

[joel009]

I was using a loop to populate the monthly charge and the annual charge.
Do Until intColCount = 145 'increased col count to go through 2014 2/23/2009 JFH
If !FDOC <= xlSheet.Cells(2, intColCount).Value And !LDOC >= xlSheet.Cells(2, intColCount).Value Then
''' xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment]
intMonthlyChargeCount = intMonthlyChargeCount + 1
End If
intLoopCounter = intLoopCounter + 1
If intLoopCounter = 13 Then
intLoopCounter = 1
intColCount = intColCount + 2
'populate yearly sum
If intMonthlyChargeCount > 0 Then
xlSheet.Cells(lngRowCount, intYearlySum) = ![Lease Payment] * intMonthlyChargeCount
End If
intMonthlyChargeCount = 0
intYearlySum = intYearlySum + 13
Else
intColCount = intColCount + 1
End If
Loop
Using the value from the Excel spreadsheet header for the month/year so it was easy to apply the math to get the values. The new template does not contain the monthly values, only the year. So I need to calculate the annual amounts from the FDOC and the LDOC which can be anywhere from 1/1/2006 - 12/31/2014. Still plugging here.

 

[Remou]

You seem to be doing some financial analysis, rather than building an application, so I suspect you need a new table on these lines:

[tt]FDOCtoLDOC
CustomerID
CYear ) This is overkill,
CMonth ) but I am not sure
StartDate ) how you want to
EndDate ) work this.
Amount[/tt]

The primary key could be CustomerID, Year, Month.

And some code to fill it:

Sub CreateFDOCtoLDOC()
Dim db As Database
Dim rsC As DAO.Recordset
Dim rsD As DAO.Recordset
Dim dteTemp As Date

Set db = CurrentDb

Set rsC = db.OpenRecordset("Customers")
Set rsD = db.OpenRecordset("FDOCtoLDOC")

Do While Not rsC.EOF
    
    dteTemp = rsC!FDOC
    
    Do While dteTemp <= rsC!LDOC
        rsD.AddNew
        rsD!CustomerID = rsC!CustomerID
        
        'Either these two lines ...
        rsD!CYear = Year(dteTemp)
        rsD!CMonth = Month(dteTemp)
        
        'Or these two lines are overkill.
        rsD!StartDate = dteTemp
        'Zeroth day = last day of previous month
        rsD!EndDate = DateSerial(Year(dteTemp), Month(dteTemp) + 1, 0)
        
        'It seems likely that you have a standard amount,
        'so you may not need this.
        rsD!Amount = rsC!Amount / DateDiff("m", rsC!FDOC, rsC!LDOC)
        rsD.Update
    
        dteTemp = DateAdd("m", 1, dteTemp)
        
    Loop
    
    rsC.MoveNext

Loop

End Sub

You can then set about your analysis with crosstabs and so forth.

http://lessthandot.com

 

[joel009]

I have the data in a table. I am looking at given an FDOC of 3/1/2007 and an LDOC of 4/30/2010 how many month are included in years 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014.

 

[Remou]

I am suggesting a new table that will hold that information. The code will fill the new table.

http://lessthandot.com

 

[joel009]

If I could fill the fields in the table I could fill the fields in the report.

 

[Remou]

I am suggesting creating a table that has a slightly more normalized format using the code I posted. This is not suitable for a report, however, it will be much easier to create a report if you have a table in the format I suggested. I do not think it is a good idea to use UDFs with the set-up you have.

You seem to have an objection to the idea I am suggesting, I wish you would say what it is.

http://lessthandot.com

 

[joel009]

I am creating a Excel report from a table. I keep getting requests to change the report about every 2 months so it is frustrating me. It is a financial report. If I redesign the table the next time they make a change request I will be reinventing the wheel again. I was hoping to use the available data in the table to write directly to the Excel spreadsheet. The same process to populate a new table with the given data elements should be able to be used to send to Excel, it is only calculations based on dates.
I have created a function to return the number of months which I will apply to the Monthly charge and write it to Excel.
Here is what I have and it seems to be working fine.
Public Function GetNumberOfMonths(Startdt As Date, Enddt As Date, Year As Integer)

'determine if Year is between FDOC and LDOC, if not value is zero
If Format(Startdt, "yyyy") > Year Then
GetNumberOfMonths = 0
Exit Function
End If

If Format(Startdt, "yyyy") = Year Then
GetNumberOfMonths = DateDiff("m", Startdt, Format(Year, "12/31/" & Year))
Exit Function
End If

If Format(Startdt, "yyyy") < Year And Format(Enddt, "yyyy") > Year Then
GetNumberOfMonths = 12
Exit Function
End If

If Format(Enddt, "yyyy") = Year Then
GetNumberOfMonths = DateDiff("m", Format(Year, "1/1/" & Year), Enddt) + 1
Exit Function
End If

If Format(Enddt, "yyyy") < Year Then
GetNumberOfMonths = 0
Exit Function
End If

End Function

I call the funtion as I am writing to Excel.

xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment]
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2006)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2007)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2008)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2009)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2010)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2011)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2012)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2013)
intColCount = intColCount + 1
xlSheet.Cells(lngRowCount, intColCount) = ![Lease Payment] * GetNumberOfMonths(!FDOC, !LDOC, 2014)

Which I can probably fit into a loop.

Thank you for letting me bounce ideas off you. I apologize for the confusion.

I'm sorry if I was unclear. In 2 months they will probably want quarterly amounts...LOL

 

[MajP]

As far as I can tell your code does not work. Unless I do not understand your calculations.

Test all cases

Public Sub test()
  'Test before 0
  Debug.Print GetNumberOfMonths(#1/1/2008#, #6/30/2008#, 2009)
  'Test after 0
  Debug.Print GetNumberOfMonths(#1/1/2010#, #6/30/2010#, 2009)
  'Test within 6
  Debug.Print GetNumberOfMonths(#1/1/2009#, #6/30/2009#, 2009)
  'Test start before end within 6
  Debug.Print GetNumberOfMonths(#3/1/2008#, #6/30/2009#, 2009)
  'test start within end after 10
  Debug.Print GetNumberOfMonths(#3/1/2009#, #6/30/2010#, 2009)
  'test boundary within 12
  Debug.Print GetNumberOfMonths(#1/1/2009#, #12/31/2009#, 2009)
  'test boundary at start 12
  Debug.Print GetNumberOfMonths(#1/1/2009#, #1/31/2010#, 2009)
  'test boundary at end 12
  Debug.Print GetNumberOfMonths(#12/1/2008#, #12/31/2009#, 2009)
End Sub

results should be
0
0
6
6
10
12
12
12

your results
0
0
11
6
9
11
11
12

This is a more efficient and correct solution

Public Function GetNumberOfMonths(Startdt As Date, Enddt As Date, dtmYear As Integer) As Integer

    Dim yearStart As Long
    Dim yearEnd As Long
    yearStart = Year(Startdt)
    yearEnd = Year(Enddt)

    'check to see if the period end before or begins after the current year
    If Not (yearStart > dtmYear Or yearEnd < dtmYear) Then
       If yearStart < dtmYear And yearEnd > dtmYear Then
         'period spans the year
         GetNumberOfMonths = 12
       ElseIf yearStart < dtmYear And yearEnd = dtmYear Then
         'start date before the year and end date in the year
         Startdt = DateSerial(dtmYear, 1, 1)
       ElseIf yearStart = dtmYear And yearEnd > dtmYear Then
         'Starts in the year and ends in another year
         Enddt = DateSerial(dtmYear, 12, 31)
       End If
      GetNumberOfMonths = DateDiff("M", Startdt, Enddt) + 1
    End If
End Function

 

[joel009]

I threw the code out there in a very rough form. I had not tested it completely.
At least we got on the same page!!! Thank you!!!!!
I was doing some testing and changed one If clause.

If Format(Startdt, "yyyy") = Year Then
GetNumberOfMonths = DateDiff("m", Startdt, Format(Year, "12/31/" & Year)) + 1
Exit Function
End If
It seems to work with this but I did not test all cases as you did. I only ran it against my data and they are all 36 month duration.

I will test your function (and mine) tomorrow as I am swamped today. It looks cleaner (that was what I was looking for). I will have to test but does your Function ever return a zero? The first If clause seems to have no action and should result in a zero.

Thank you for your patience.

Joel

 

[MajP]

If you do not declare a return variable type, your function will return a variant. A variant defaults to null. This is sloppy coding. You should explicitly declare your variables. I can not imagine you ever want to return a null.

In my functon I return a integer data type.

Public Function GetNumberOfMonths(Startdt As Date, Enddt As Date, dtmYear As Integer) As Integer

An integer data type defaults to 0. Therefore I never need to set it to zero.

Usually setting values unnecessarily is considered bad coding. For example

dim x as integer
if A = b then
x = 1
else
x = 0
end if

is the same as

dim x as integer
if A = b then
x = 1
end if

Even if you modify your case your code will still not work. The individual if's with exit function statements is really sloppy programming. Look what happens if both the start date and the end date happen in the same year. It kicks out after the start date check and you never even get to

If Format(Enddt, "yyyy") = Year Then
GetNumberOfMonths = DateDiff("m", Format(Year, "1/1/" & Year), Enddt) + 1
Exit Function
End If

I strongly recommend scrapping your function and using mine. Mine is correct.

 

[MajP]

Oops sorry for sounding critical, did not mean to. I was just having flashbacks from my school days. When I was taking Java my instructor would ding us severely every time we wrote unnecessary code, imprecise boolean logic, or improperly scoped/declared variables. It was beaten into me so much, I have trouble not fixing it.

 

[ZmrAbdulla]

This is another one

Public Function GetNumberOfMonths(ByVal stDate As Date, ByVal enDate As Date)
    Dim nYears As Integer
    nYears = DateDiff("yyyy", stDate, enDate)
    Debug.Print "Number of Years : "; nYears
    Dim x As Integer
    Dim nstDate As Date
    Dim nenDate As Date
    nstDate = stDate
    For x = 0 To nYears
        nenDate = IIf(DateSerial(Year(nstDate), 12, 31) < enDate, DateSerial(Year(nstDate), 12, 31), enDate)
        Debug.Print "Months Between " & nstDate & " And " & nenDate & " : " & DateDiff("m", nstDate, nenDate) + 1
        nstDate = DateSerial(Year(nenDate) + 1, 1, 1)

    Next

End Function

You may call it like

?GetNumberOfMonths(#10/01/2001#,#10/31/2009#)

will return

Number of Years :  8 
Months Between 10/1/2001 And 12/31/2001 : 3
Months Between 1/1/2002 And 12/31/2002 : 12
Months Between 1/1/2003 And 12/31/2003 : 12
Months Between 1/1/2004 And 12/31/2004 : 12
Months Between 1/1/2005 And 12/31/2005 : 12
Months Between 1/1/2006 And 12/31/2006 : 12
Months Between 1/1/2007 And 12/31/2007 : 12
Months Between 1/1/2008 And 12/31/2008 : 12
Months Between 1/1/2009 And 10/31/2009 : 10

Zameer Abdulla

 

[joel009]

MajP - Thanks, that works perfectly. I was taught to NEVER use IF - ELSEIF statements, always use a SELECT CASE. Sometimes that is not possible. As always I am rushed and I knew my rough code was not complete but I was having trouble connecting the dots. Thank you!!!.

Remou - I am sorry for the confusion, you have always been helpful in the past.

 

[joel009]

MajP - I actually had to change it to get 12 months.

Dim yearStart As Long
Dim yearEnd As Long
yearStart = Year(Startdt)
yearEnd = Year(Enddt)

'check to see if the period end before or begins after the current year
If Not (yearStart > dtmYear Or yearEnd < dtmYear) Then
If yearStart < dtmYear And yearEnd > dtmYear Then
'period spans the year
GetNumberOfMonths = 12
Exit Function
ElseIf yearStart < dtmYear And yearEnd = dtmYear Then
'start date before the year and end date in the year
Startdt = DateSerial(dtmYear, 1, 1)
ElseIf yearStart = dtmYear And yearEnd > dtmYear Then
'Starts in the year and ends in another year
Enddt = DateSerial(dtmYear, 12, 31)
End If
GetNumberOfMonths = DateDiff("M", Startdt, Enddt) + 1
End If
If I didn't it would return the total term of the lease.

 

[MajP]

Good catch. Bottom line you have to always check the five cases.

Period ends before the range starts
Period starts before the range ends inside the range
Period starts and ends inside the range
Period starts in the range and ends outside the range
Period starts outside the range

Not sure who told you to always use a select case beside an if then else. Both constructs short circuit. That means if a condition is met it falls outside the check and does not keep checking. But they are good for different things. Selected cases work well with categories but not so well with nested conditions or complex boolean expressions. The above would be difficult to write using a select case.