M
Mymind
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.
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.