software

Hi there,

I am trying to get a difference between two months, I looked every
where in excel date functions and on excel groups to no avail.

I came across the datedif function but for some reason this function
works very strange. lets say I have a date in cell A1 as 12/31/2005 and
the date in cell B1 is 2/1/2006. now the difference in months should be
2 and not 1 as this function indicates. when I use 12/1/2005 as my
start date then I get 2 as an answer, not sure why the inconsistency.

this is the current formula --gt; DATEDIF(A1,B1,quot;mquot;)

I realize that 12/31 is the end of the month for december and therfore
it is caluclating the month difference between 12/31 and 2/1 as one
month. but I want the difference in months, so if I have 12/1 or 12/31
as my starting date and my ending date is 2/1 or 2/28, I still want the
difference to show as 2.

I would appreciate any feedback/input for this problem..

thanks a lot...datedif() gives you the # of complete months between 2 dates, so in your
example 1 is the correct answer.

For more information about datedif and dates generally, see Chip Pearson's
site:

www.cpearson.com/excel/datedif.htmquot;leitek.comquot; wrote:

gt; Hi there,
gt;
gt; I am trying to get a difference between two months, I looked every
gt; where in excel date functions and on excel groups to no avail.
gt;
gt; I came across the datedif function but for some reason this function
gt; works very strange. lets say I have a date in cell A1 as 12/31/2005 and
gt; the date in cell B1 is 2/1/2006. now the difference in months should be
gt; 2 and not 1 as this function indicates. when I use 12/1/2005 as my
gt; start date then I get 2 as an answer, not sure why the inconsistency.
gt;
gt; this is the current formula --gt; DATEDIF(A1,B1,quot;mquot;)
gt;
gt; I realize that 12/31 is the end of the month for december and therfore
gt; it is caluclating the month difference between 12/31 and 2/1 as one
gt; month. but I want the difference in months, so if I have 12/1 or 12/31
gt; as my starting date and my ending date is 2/1 or 2/28, I still want the
gt; difference to show as 2.
gt;
gt; I would appreciate any feedback/input for this problem..
gt;
gt; thanks a lot...
gt;
gt;

If you subtract two dates then you will get the number of days between
them, but, how many days are in a month? Do you take it as 365/12 ? If
so, then this formula might give you what you want:

=INT((A1 - B1 365/12)*12/365)

Hope this helps.

Pete=(YEAR(B1)*12 MONTH(B1))-(YEAR(A1)*12 MONTH(A1))

Format as Number, not as Date (which Excel will do automatically)

--
Kind regards,

Niek Otten

quot;leitek.comquot; gt; wrote in message ups.com...
gt; Hi there,
gt;
gt; I am trying to get a difference between two months, I looked every
gt; where in excel date functions and on excel groups to no avail.
gt;
gt; I came across the datedif function but for some reason this function
gt; works very strange. lets say I have a date in cell A1 as 12/31/2005 and
gt; the date in cell B1 is 2/1/2006. now the difference in months should be
gt; 2 and not 1 as this function indicates. when I use 12/1/2005 as my
gt; start date then I get 2 as an answer, not sure why the inconsistency.
gt;
gt; this is the current formula --gt; DATEDIF(A1,B1,quot;mquot;)
gt;
gt; I realize that 12/31 is the end of the month for december and therfore
gt; it is caluclating the month difference between 12/31 and 2/1 as one
gt; month. but I want the difference in months, so if I have 12/1 or 12/31
gt; as my starting date and my ending date is 2/1 or 2/28, I still want the
gt; difference to show as 2.
gt;
gt; I would appreciate any feedback/input for this problem..
gt;
gt; thanks a lot...
gt;

Her's another way

=DATEDIF(A1,B1,quot;mquot;) (DAY(A1)gt;DAY(B1))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516236I appreciate all of your help. the formula below worked for me...

thanks,
salah

daddylonglegs wrote:
gt; Her's another way
gt;
gt; =DATEDIF(A1,B1,quot;mquot;) (DAY(A1)gt;DAY(B1))
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=516236