If Statement Help!!

[Guest]

Hello,

I am a novice with Excel when it comes to formulas and VBA. My question is
this:

I have a spreadsheet that has a field called "Premium Adjustment". This
spreadsheet is used to adjust the Insurance premiums for flood insurance and
needs to calculate the premium for both Full Month and Partial Month. What I
am trying to do is use the formula for partial month but for a full month I
need one field (premium amount) to calculate different. In other words, If
Date Difference is 28, 30 or 31 then Premium Amount calculates using this
formula =SUM(V47:W47) instead of this =SUM(X48/360)*(Q48). How can I get this
to work? Please be sure to simplfy answer.

All help would be most appreciated.

Thanks!!

 

[Guest]

I've used cell A3 as the cell that contains the date. This will only work if
the cell has the full date (m/d/y)

The formula is...
=IF(MONTH(A3)=MONTH(A3+1),SUM(X48/360)*(Q48),SUM(V47:W47))

What this does is take the date provided and add one day. If the new date
(after adding a day) is in the same month as the provided date then it will
calculate the partial month. If the new date is in a different month then
the provided date is the last day of the month and the full month will be
used. This will take into account leap years.

HTH,
Jayson

 

[Guest]

Hi JNW, thank you for responding.

I may have not explained or maybe I am not understanding correctly. The user
will put in the current date and the ending date both of which will have the
M/D/Y for the same month. The Premium Adjustment field then calculates the
number of days between the two dates and from this field if the number of
days is 16 then use SUM(X48/360), if it is a full month 28 (for February) 30
or 31 then use SUM(V47:W47). I am not sure how I have it recognize February
as a full month versus it thinking a partial month but if that needs to be
handled manually, it's no problem for one month of the year. Am I making
sense?

Thanks!

 

[Guest]

I think the formula I provided will work. What it does is make sure that
ending date that was entered is the last day of the month. If it is then it
uses SUM(V47:W47), if not then it is a partial month and uses SUM(X48/360).

The benefit to the formula is that you don't have to worry about manually
deciding whether or not a month has 28, 29, 30, or 31 days in it. The
formula will know if the day entered is the last day of the month. Just make
sure that you replace A3 with the cell that contains the ending date.

Try it and let me know if it doesn't work.

 

[Guest]

I think I see what your saying now, the light bulb just took a minute to come
on. So do I place this formula in the cell containing my End Date? I believe
so but want to double check before doing anything.

Thanks!!

 

[Guest]

I'm sorry, I meant my Premium Adjusment field. That's the field that reads
the number of days between Start date and Ending Date.

 

[Guest]

You can't put it in the cell containing your end date. If you did you
wouldn't be able to enter the end date. I imagine you already have a
cell that counts the days or does the calculation you are looking for here.
Just replace that formula. And make sure that you replace the cell reference
A3 with the cell that contains the real end date.

 

[Guest]

It appears to be moving the formula to the next cell. Example: If I place it
in Date Differance cell Q101, the reads Q102. My dates are 06/01/2007 -
07/01/2007 and my number of dats come out to 210.

 

[Guest]

Without seeing the workbook there isn't much I can do. I don't know what you
are adding or dividing by 360 originally. All the formula is doing is
choosing whether you need to count the days or use the full month based on
what end date the user inputs. I suspect you already had the two formulas in
the sheet because you provided them to me.

 

[Guest]

I know, it's hard sometimes when you can't see what the other person is
doing. I appreciate you trying though! I will do a work around which will
work, I was just trying to make it a bit easier.

Thank you again!!!