Difference between dates returning full months and remaining days

[kvan]

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((EOMONTH(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!

 

[Sean Timmons]

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

 

[David Biddulph]

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

 

[Ron Rosenfeld]

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((EOMONTH(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!

This is relatively easy to do using VBA to produce a user defined function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.

Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like


=CalMonths(A1,A2,"M") or
=CalMOnths(A1,A2,"D") in some cell.

where A1 is the start date, and A2 is the End date.

The M and D argument is optional -- if omitted, it defaults to M.

End date must be later than Start Date.

The above does an "inclusive" count. In other words, with Start of 1 Jan 2009
and End of 31 Jan 2009, the function will return 1 month (rather than 30 days).

If this is not what you want, you can just "add one" to whichever date you want
to omit in calculating your differences.

========================================================
Option Explicit
Function CalMonths(StartDt As Date, EndDt As Date, Optional Res As String =
"M")
Dim dFirstMonth As Date, dLastMonth As Date

'check for valid entries
If Not UCase(Res) Like "[MD]" Then
CalMonths = CVErr(xlErrValue)
Exit Function
End If

If Not EndDt > StartDt Then
CalMonths = CVErr(xlErrValue)
Exit Function
End If

'adjust starting and ending month dates so as to include
'only full months
If Day(StartDt) = 1 Then
dFirstMonth = StartDt
Else
dFirstMonth = DateSerial(Year(StartDt), Month(StartDt) + 1, 1)
End If

If Month(EndDt + 1) <> Month(EndDt) Then
dLastMonth = EndDt
Else
dLastMonth = EndDt - Day(EndDt)
End If

'Calculate difference in months
CalMonths = DateDiff("m", dFirstMonth, dLastMonth) + 1

'Or do we want days?
If UCase(Res) = "D" Then
CalMonths = dFirstMonth - StartDt + EndDt - dLastMonth
End If

End Function
=============================
--ron

 

[Ron Rosenfeld]

=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

 

[Ron Rosenfeld]

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

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

 

[Nooruddin Surani]

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/excel-datediff-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((EOMONTH(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/tutorial...6-8e97f9431be0/ping-utility-and-webservi.aspx