RE: Function to calculate yearly billing amount

That would take a little additional coding. Basically, what you need to do
is check the start and end dates and see if the years are the same. If they
are not, then use start date through 12/31/firstyear then 1/1/nextyear
through end date. If it can span more than 2 year, it gets a little more
complicated. How are your VBA skills?

"Mymind" wrote:

I am able to get the number of months between using the formula:
*MonthsBetween = DateDiff(“m”,StartDate,StopDate)

What I am stuck getting is the number of months within each year within the
date range specified by the startdate and stopdate. See the example, I wnat
to be able to get the numbers 7, 12, and 3.

Example:
ID 3 has three years that occur between StartDate and StopDate
2003 has 7 months * MonthAmt($565.06) = $3,955.43,
2004 has 12 months * MonthAmt($565.06) = $6,780.74,
2005 has 3 months * MonthAmt($565.06) = $1,695.18



"Klatuu" wrote:

You can use this to find the number of months.
Month(StopDate) - Month(StartDate)

In your first record, it will return 11, so if you want it to return 12, then

Month(StopDate) - Month(StartDate) + 1

"Mymind" wrote:

I am trying to calculate a year charge amount for each year between two
dates. I have the following data:

ID 1
Tot_BillingAmt $198,810.25
StartDate 01/01/2001
StopDate 12/31/2001
MonthsBetween* 12
MonthAmt* $16,567.52

ID 2
Tot_BillingAmt $18,500.00
StartDate 07/01/2002
StopDate 10/31/2003
MonthsBetween* 16
MonthAmt* $13.718.75

ID 3
Tot_BillingAmt $12,431.35
StartDate 06/01/2003
StopDate 03/31/2005
MonthsBetween* 22
MonthAmt* $565.06

*MonthsBetween = DateDiff(“m”,StartDate,StopDate)
*MonthAmt = Tot_BillingAmt / MonthsBetween

*Yearly Billing Amt = MonthAmt * The number of months for that year

Example:
ID 3 has three years that occur between StartDate and StopDate
2003 has 7 months * MonthAmt($565.06) = $3,955.43,
2004 has 12 months * MonthAmt($565.06) = $6,780.74,
2005 has 3 months * MonthAmt($565.06) = $1,695.18

I can not figure out how to calculate The number of months for each year
represented within the record. Does anybody have any suggestions or can
point me in the right direction. I figure I probably have to create a VB
function.

Any help is gretly appreciated.



.

Relevant Pages

  • RE: Function to calculate yearly billing amount
    ... "Mymind" wrote: ... Only output record:Billyear = Yearand nummonths= MonthsBetween. ... ID 3 has three years that occur between StartDate and StopDate ...
    (microsoft.public.access.modulesdaovba)
  • RE: Function to calculate yearly billing amount
    ... What I am stuck getting is the number of months within each year within the ... ID 3 has three years that occur between StartDate and StopDate ... In your first record, it will return 11, so if you want it to return 12, then ...
    (microsoft.public.access.modulesdaovba)
  • Selecting adjacent cell
    ... The temp data starts at cell c3 and humidity starts ... Application.WorksheetFunction.Max.cell.offset' Temp ... Stopdate = Startdate + 1 ... If Startdate <= Stopdate Then ...
    (microsoft.public.excel.programming)
  • [hibernate] many-to-many with additional data
    ... I've got following relational structure: ... (startDate and stopDate)? ... Pawel Rozynek ...
    (comp.lang.java.databases)