About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
To detect month or days or year in if formula	ShamsulZ	Excel Discussion (Misc queries)	1	November 6th 06 07:47 AM
Get Data based on Month and Year	karstens	Excel Worksheet Functions	1	August 2nd 06 12:42 AM
I'm looking for a formula to calc # of days left in a month	Manthony	Excel Worksheet Functions	3	June 6th 06 03:15 PM
Locate month n year from range of date	Rao Ratan Singh	New Users to Excel	1	March 2nd 06 09:13 AM
count dates within range by year and month	Isaiah25	Excel Worksheet Functions	10	May 4th 05 10:22 PM

Menu

About Us