=IF(B14<F14,DATE(YEAR(G14),MONTH(G14)+J14,DAY(G14) ),DATE(YEAR(C14),MONTH(C14)+J14,DAY(C14)))
will let you put a date in G14 & C14, instead of splitting it between G/H/J
and C/D/E
--
David Biddulph

"Charlie" wrote in message
...
Hi Rick,

Thanks for the explanation....your answer did the trick and i solved the
problem, but another mess i have landed in now.....can you check if
anything
you can suggest to help me out on this one

i am using the formula
=IF(B14<F14,DATE(G14,H14+J14,I14),DATE(C14,D14+J14 ,E14))

to add on months (calculated from the previous formula) to a date but if i
add the date in the format 31-dec-2005, i cannot get it hence i have to
enter
dates in seperate columns with the Year in one column, date in one column
and
month in one column....as my date is not constant this has made my
worksheet
look extremely unprofessional....any help!!!!!

thanks once again




"Rick Rothstein (MVP - VB)" wrote:

Thanks a lot Rick i had already figured out that i can use the +1
fix...
but doesnt it sound weird that Excel cannot calculate

But you aren't asking Excel to count months... you are asking to subtract
two numbers (that **you** associate as months). Just like 3-1 equals 2
(the
difference) and not 3 (the count of digits involved), that is what your
formula is asking Excel to do... so you have to add the one to get what
you
are looking for. If you do any programming, you run into this same
situation
with arrays. Say you have an array with indexes of 1, 2 and 3. The upper
bound for the array is 3 and the lower bound for the array is 1... the
difference (3-1=2) is **not** the count of the elements... it is one too
few, so you have to add one to the difference to get the actual count of
elements. This "problem" crops up all the time, in many different
areas....
it's just the way our number system works and, so, we are stuck with it.

and yes you are right my start date is always the beginning of
the month and my finish date is always the end of the month...
for your answer to the 4 sample date ranges you sent i would
expect the following answers

15 October 2007 and 15 December 2007 2 months
30 October 2007 and 01 December 2007 2 months
31 January 2008 and 28 February 2008 1 month
31 January 2008 and 01 March 2008 1 month

I wasn't completely clear on your needs here... do you need any help with
adjusting dates to beginning and end of months? By the way, I was
surprised
at your answers to my date range questions above... if you adjust those
dates to the beginning and end of their respective months, wouldn't your
answers be 1 greater than what you showed? Or did your earlier comment
mean
to imply you would never see intermediated dates like I proposed?

Rick