DateDiff - Whole Month Problem

[J-P-W]

Hi,

I have a system that records insurance policies.

If the policy is cancelled then any part of a month insured is deducted
from the premium, so a policy that ran for 32 days would get a 10 month
rebate so would 58 days etc.

I've used:

TheStartDate = "2006-06-15" 'changed manually for now, usually ......
rsuser("PolicyStart")
DaysSinceInception = DateDiff("d",TheStartDate,Date)
MonthsSinceInception = DateDiff("m",TheStartDate,Date)

response.Write("Policy Start = " & TheStartDate & "<br>")
response.Write("Today = " & date & "<br>")
response.Write("Days Since Inception = " & DaysSinceInception & "<br>")
response.Write("Months Since Inception = " & MonthsSinceInception &
"<br>")
Example of results (Date is in UK format):

Policy Start = 2006-06-01
Today = 14/07/2006
Days Since Inception = 43
Months Since Inception = 1

Policy Start = 2006-06-11
Today = 14/07/2006
Days Since Inception = 33
Months Since Inception = 1

Policy Start = 2006-06-13
Today = 14/07/2006
Days Since Inception = 31
Months Since Inception = 1

Policy Start = 2006-06-15
Today = 14/07/2006
Days Since Inception = 29
Months Since Inception = 1

Policy Start = 2006-06-30
Today = 14/07/2006
Days Since Inception = 14
Months Since Inception = 1

~~~~~~~~~~~

Can anyone please help me to 'round up' the month?

In antipation, thank you

Jon

 

[Ray Costanzo [MVP]]

Hi Jon,

Would this work? Just jump ahead a year to get the renewal date (or
whatever it may be called) and then count the month difference from that
date and today's date:


Function PolicyRefundMonthCount(InceptionDate)
Dim dRenewalDate
dRenewalDate = DateAdd("yyyy", 1, InceptionDate)
PolicyRefundMonthCount = DateDiff("M", Date, dRenewalDate)
End Function

Results with your test dates:
2006-06-01: 11
2006-06-11: 11
2006-06-13: 11
2006-06-15: 11
2006-06-30: 11

Ray at work

 

[J-P-W]

Hi Ray,

Looked really good, but it has given me some of the same results, only
backwards.

I gave a start date of 10th August 05, so less than one month to go,
341 days gone, your function gave me 1 month as the result, but 24 days
needs to be less than 1!!!

I tried playing around with 13 - result and 12 - result - no good
either!!

Never mind, thanks for your efforts.

Maybe I'll just use multiple of 30 days

Jon

 

[Bob Barrows [MVP]]

Hi Ray,

Looked really good, but it has given me some of the same results, only
backwards.

I gave a start date of 10th August 05, so less than one month to go,
341 days gone, your function gave me 1 month as the result, but 24
days needs to be less than 1!!!

I tried playing around with 13 - result and 12 - result - no good
either!!

Never mind, thanks for your efforts.

Maybe I'll just use multiple of 30 days

Jon

Use "d" instead of "m" in the DateDiff function to cause it to return te
number of days. Then divide by 30 ...

 

[J-P-W]

Indeed, I think I'll have to do that, thanks, Jon