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 -- DATEDIF(A1,B1,"m")

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:

http://www.cpearson.com/excel/datedif.htm


"leitek.com" wrote:

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 -- DATEDIF(A1,B1,"m")

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...

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

"leitek.com" wrote in message
ups.com...
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 -- DATEDIF(A1,B1,"m")

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...

Her's another way

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516236

I appreciate all of your help. the formula below worked for me...

thanks,
salah

daddylonglegs wrote:
Her's another way

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516236