472,805 Members | 862 Online

# DateDiff - Whole Month Problem

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

~~~~~~~~~~~

In antipation, thank you

Jon

Jul 14 '06 #1
4 4410
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
PolicyRefundMonthCount = DateDiff("M", Date, dRenewalDate)
End Function

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" <jo******@gmail.comwrote in message
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

~~~~~~~~~~~

In antipation, thank you

Jon

Jul 17 '06 #2
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

Jul 17 '06 #3
J-P-W wrote:
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 ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 17 '06 #4
Indeed, I think I'll have to do that, thanks, Jon

Jul 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.