ExcelBanter - View Single Post - Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..)

Ron Rosenfeld

On Mon, 01 Oct 2007 11:30:36 -0700, wrote:

First, Chip and Ron de Bruin, thanks for your answers. You definetly
got me thinking about this one, and pointed me in the right direction.
Chip, I played with your suggestion over the weekend, but I was unable
to get it working for whatever reason. Also, it was the WEEKEND, so I
tried not to spend a whole lot of time on it. :)

Next, to Ron R: Thanks!, it looks like that's almost got it. If I
enter in dates that are 1 or more months apart, it seems to be giving
me the correct pricing. The issue, as far as I can tell is the
DATEDIF(Start Date, End Date, "m") errors out if in the same month. In
my sample sheet, I had 01/01/07 to 01/15/07, which cannot be expressed
in months (apparently.) Can you suggest a correct/appropriate IF
statement in that same cell to check this, and work around it? It
looks like it actually works all the rest of the time however, which
is amazing.

Here's the formula I have now, all in one of the results cells: [Watch
out, spammy!]
=(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))+(DAY($H$7) *H12/DAY(DATE(YEAR($H
$7),MONTH($H$7)+1,0)))+(H12*DATEDIF(DATE(YEAR($B$ 7),MONTH($B
$7)+1,1),DATE(YEAR($H$7),MONTH($H$7),1),"m"))

I could fork that with an IF, and have it just do the first calc,
[(DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)-DAY($B$7)+1)*H12/
DAY(DATE(YEAR($B$7),MONTH($B$7)+1,0)))] if it's in the same month, and
if not, then do the rest. It also seems to work correctly with
January, then February, which I thought might also cause a problem..
but looks like it's ok for whatever reason.

So I imagine the logic test function isn't SAMEMONTH($B$7, $H$7).. Any
Ideas?

Thanks again to everyone involved with this thread. I'm so excited
this may finally work!

-John, retailmessiah

As I wrote, my first choice would be to use the UDF. It seems simpler to me.

If you absolutely must use a formula solution, you can change the formula for
REST:

=IF(ISERR(DATEDIF(DATE(YEAR(Start),MONTH(Start)+1, 1),
DATE(YEAR(End),MONTH(End),1),"m")),-Monthly,Monthly*
DATEDIF(DATE(YEAR(Start),MONTH(Start)+1,1),DATE(YE AR(End),MONTH(End),1),"m"))

The problem is that the DATEDIF worksheet formula will give an error is the
first argument is later than the second argument. So we test for the error,
and act accordingly.

This is not a problem with the VBA DATEDIFF function, as it will give a
negative result in that instance.
--ron