DateDiff - Whole Month Problem

14 Jul 2006 10:17 PM

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 & " ")
response.Write("Today = " & date & " ")
response.Write("Days Since Inception = " & DaysSinceInception & " ")

response.Write("Months Since Inception = " & MonthsSinceInception &
" ")
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

17 Jul 2006 1:26 PM

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

Show quote

"J-P-W" <jonpw***@gmail.com> wrote in message
news:1152915447.706631.186220@m73g2000cwd.googlegroups.com...
> 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 & " ")
> response.Write("Today = " & date & " ")
> response.Write("Days Since Inception = " & DaysSinceInception & " ")
> response.Write("Months Since Inception = " & MonthsSinceInception &
> " ")
> 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
>

17 Jul 2006 9:41 PM

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

17 Jul 2006 9:50 PM

Bob Barrows [MVP]

J-P-W wrote:
Show quote

> 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"

17 Jul 2006 9:57 PM

J-P-W

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