VBA Calculation to distinguish Full and Partial Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am working on a database to figure out Insurance premiums. by plugging in
the beginning date and end date the number of days between the two days will
figure out how manys to charge for. What I need is the formula for the
following:

1. If date difference is 28, 30 or 31 then A*B (this calculates a full
month) else (C/360)*(D) (this calculates a partial month.

2. Also, what if we are in march and I have a date difference of 28. How do
I get this to work where the database will not read 28 as a full month or the
month of February?

3. Would I create an unbound text box and place the formula for each in the
control source or do it in VBA?

Please simplify answer for example: If I am to use VBA, where would I place
the formula and what event and all that good stuff. I am somewhat familar but
an expert and that is why I need the help of this forum. You guys have never
steered me and always come through for me.

Thank you!!!!
 
The DateDiff function is not affected by the month unless you are using it in
the calculatioin.
Your 28, 30, or 31 (where is 29 for leap year) is not clear. What are the
rules for choosing one of these values? The way you are looking at it, the
beginning date would have to be on the 1st of a month for it to have any
bearing.
If you can clarify the business rules, the calculation should not be
difficult.
 
The 28, 30, 31 and 29 for leap year (forgot that, thanks) represents a full
month. So if we need to force place flood insurance from beginning date
3/1/07 - ending date 3/31/07 that would be a full month and use the full
month calculation below. However, if I need it from 3/1/07 - 3/28/07, this
would be a partial month so how do I know the database won't calculate it as
a full month thinking it's February based on the date difference. If the date
difference field is 28 days, another field will calculate based on the 28
days or whatever number of days are in the date difference field.

Does this help at all? If not, please clarify "Business Rule" and what it is
your asking for and maybe I can help better to answer.

Thanks!!
 
Use the month and year function to get the current month & year, use that in
a format function and put a place holder of 1 for the date. Then use the
datediff to get the number of days since the beginning of the month.
 
Here is a calculation that will always return the last day of the month
regardless of the number of days in the month even for leap years:
= DateDerial(Year(dtmSomeDate),Month(dtmSomeDate) + 1, 0)

If dtmSomeDAte = 3/1/07, then it will return 3/31/2007
You can use this to check the end date and determine whether it is a whole
or partial month.
 
Back
Top