Function MonthFrac(StartDate As Date, EndDate As Date) As Single
* '= siti Vi / Jakarta 12 July 2011*
Dim YY1 As Integer, MM1 As Integer, DD1 As Integer
Dim YY2 As Integer, MM2 As Integer, DD2 As Integer
Dim MM As Integer, DD As Integer, EndMonthDays As Integer
YY1 = Year(StartDate): YY2 = Year(EndDate)
MM1 = Month(StartDate): MM2 = Month(EndDate)
DD1 = Day(StartDate): DD2 = Day(EndDate)
If DD2 < DD1 Then
DD2 = DD2 + Day(DateSerial(YY2, MM2, 0))
MM2 = MM2 - 1
End If
If MM2 < MM1 Then
MM2 = MM2 + 12
YY2 = YY2 - 1
End If
EndMonthDays = Day(DateSerial(YY2, MM2 + 1, 0))
MM = (YY2 - YY1) * 12 + (MM2 - MM1)
DD = (DD2 - DD1)
MonthFrac = MM + (DD / EndMonthDays)
End Function
On Tue, Jul 12, 2011 at 7:55 PM, Richard <richard.m...@gmail.com> wrote:
> Hi All,
> I would appreciate a vba function that will calculation the number of
months
> and fraction of a month between 2 dates.
>
> i.e.
> January 1, 2011 to February 14, 2011 would be 1.5 months
> January 1, 2011 to February 15, 2011 would be 1.5335714
> January 1, 2011 to March 15, 2011 would be 2.483871
>
> Thank you in advance
>
> Rich
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
sitiVi_UDF_MonthFrac.xls
Description: MS-Excel spreadsheet
