I'm trying to calculate difference between dates, but with a twist- I need to
return the number of full months between beginning and end dates, then, in
the next cell over, the remaining days. Here's where I am so far
(M=BeginDate, N=EndDate) for Months:
=IF(OR(M13="",N13=""),"",IF((DAY(M13)=1),(YEAR(N13 )-YEAR(M13))*12+MONTH(N13)-MONTH(M13)+1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)))

Days:
=IF(OR(M13="",DAY(M13)=1),"",IF(N13="","",DAY((EOM ONTH(M13,0))-DAY(M13)+1)))

This is returning properly only if EndDate is the last day of a month. I
need it to work regardless of when the month the EndDate falls. Any
suggestions? Any help is much appreciated!

=IF(DAY(N13)<DAY(M13),(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)-1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13))

seems to get numbero f months

and

=IF(DAY(N13)-DAY(M13)0,DAY(N13)-DAY(M13),EOMONTH(M13,0)-M13+DAY(N13))

seems to get # of days.

"kvan" wrote:

I'm trying to calculate difference between dates, but with a twist- I need to
return the number of full months between beginning and end dates, then, in
the next cell over, the remaining days. Here's where I am so far
(M=BeginDate, N=EndDate) for Months:
=IF(OR(M13="",N13=""),"",IF((DAY(M13)=1),(YEAR(N13 )-YEAR(M13))*12+MONTH(N13)-MONTH(M13)+1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)))

Days:
=IF(OR(M13="",DAY(M13)=1),"",IF(N13="","",DAY((EOM ONTH(M13,0))-DAY(M13)+1)))

This is returning properly only if EndDate is the last day of a month. I
need it to work regardless of when the month the EndDate falls. Any
suggestions? Any help is much appreciated!

It sounds as if you may want to use the DATEDIF function (the one Excel
function not covered in Excel help).

http://www.cpearson.com/excel/datedif.aspx
--
David Biddulph

"kvan" wrote in message
...
I'm trying to calculate difference between dates, but with a twist- I need
to
return the number of full months between beginning and end dates, then, in
the next cell over, the remaining days. Here's where I am so far
(M=BeginDate, N=EndDate) for Months:
=IF(OR(M13="",N13=""),"",IF((DAY(M13)=1),(YEAR(N13 )-YEAR(M13))*12+MONTH(N13)-MONTH(M13)+1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)))

Days:
=IF(OR(M13="",DAY(M13)=1),"",IF(N13="","",DAY((EOM ONTH(M13,0))-DAY(M13)+1)))

This is returning properly only if EndDate is the last day of a month. I
need it to work regardless of when the month the EndDate falls. Any
suggestions? Any help is much appreciated!

On Mon, 28 Sep 2009 13:49:02 -0700, Sean Timmons
wrote:

=IF(DAY(N13)<DAY(M13),(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)-1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13))

seems to get numbero f months

and

=IF(DAY(N13)-DAY(M13)0,DAY(N13)-DAY(M13),EOMONTH(M13,0)-M13+DAY(N13))

Start Date 15 Jan 2009
End Date 15 Feb 2009

Your formulas -- 1 month
31 days

--ron

On Mon, 28 Sep 2009 21:54:52 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

It sounds as if you may want to use the DATEDIF function (the one Excel
function not covered in Excel help).

http://www.cpearson.com/excel/datedif.aspx
--
David Biddulph

DateDif doesn't do calendar months, as far as I know. Also, it seems to be
broken in the latest service pack for Excel 2007.
--ron

You can achieve your result by using the following function, DATEDIF; this function can calculate full months and days over the month as you require.

SYNTAX: =DATEDIF(start_date, end_date, "interval")

Interval may be "d", "m", "y", "md", "ym" which means day, month, year, days over month, months over year.

You can visit the following link for a thorough understanding of the same:

http://www.slideshare.net/nsurani/ex...ediff-function




kvan wrote:

Difference between dates returning full months and remaining days
28-Sep-09

I'm trying to calculate difference between dates, but with a twist- I need to
return the number of full months between beginning and end dates, then, in
the next cell over, the remaining days. Here's where I am so far
(M=BeginDate, N=EndDate) for Months:
=IF(OR(M13="",N13=""),"",IF((DAY(M13)=1),(YEAR(N13 )-YEAR(M13))*12+MONTH(N13)-MONTH(M13)+1,(YEAR(N13)-YEAR(M13))*12+MONTH(N13)-MONTH(M13)))

Days:
=IF(OR(M13="",DAY(M13)=1),"",IF(N13="","",DAY((EOM ONTH(M13,0))-DAY(M13)+1)))

This is returning properly only if EndDate is the last day of a month. I
need it to work regardless of when the month the EndDate falls. Any
suggestions? Any help is much appreciated!

EggHeadCafe - Software Developer Portal of Choice
Ping Utility and WebService in C#
http://www.eggheadcafe.com/tutorials...-webservi.aspx