![]() |
FORMULA NEEDED FOR MONTHS
Hi,
There's no formula that I know of that will cater for what you are looking for. What you can use is a VBA function to calculate the month difference. Try something like the below function (paste the below into a module) 'To use on an Excel worksheet type =Months(A1,D1) where A1 represents the first date 'and D1 represents the second date Public Function Months(Date1 As Date, Date2 As Date) As Long Dim iMonthCount As Long, sYear As Long, eYear As Long, sMonth As Long, eMonth As Long sMonth = Month(Date1) eMonth = Month(Date2) sYear = Year(Date1) eYear = Year(Date2) If sMonth = eMonth Then iMonthCount = Abs(sMonth - eMonth) If sYear eYear Then iMonthCount = ((sYear - eYear) * 12) + iMonthCount ElseIf sYear < eYear Then iMonthCount = ((Abs(sYear - eYear)) * 12) - iMonthCount End If ElseIf eMonth sMonth Then iMonthCount = Abs(eMonth - sMonth) If sYear eYear Then iMonthCount = ((sYear - eYear) * 12) - iMonthCount ElseIf sYear < eYear Then iMonthCount = ((Abs(sYear - eYear)) * 12) + iMonthCount End If End If Months = iMonthCount End Function -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200906/1 |
FORMULA NEEDED FOR MONTHS
No need for the VBA code. There is an undocumented function in Excel
called DATEDIF that will calculate the interval between two dates in a variety of intervals. E.g., =DATEDIF(StartDate,EndDate,"m") This is not to be confused with the VBA DateDiff function. For more info about DATEDIF, see www.cpearson.com/Excel/DateDif.aspx For reasons known only to Microsoft, they don't document this rather useful function. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 20 Jun 2009 13:28:48 GMT, "Alan McQ via OfficeKB.com" <u22393@uwe wrote: Hi, There's no formula that I know of that will cater for what you are looking for. What you can use is a VBA function to calculate the month difference. Try something like the below function (paste the below into a module) 'To use on an Excel worksheet type =Months(A1,D1) where A1 represents the first date 'and D1 represents the second date Public Function Months(Date1 As Date, Date2 As Date) As Long Dim iMonthCount As Long, sYear As Long, eYear As Long, sMonth As Long, eMonth As Long sMonth = Month(Date1) eMonth = Month(Date2) sYear = Year(Date1) eYear = Year(Date2) If sMonth = eMonth Then iMonthCount = Abs(sMonth - eMonth) If sYear eYear Then iMonthCount = ((sYear - eYear) * 12) + iMonthCount ElseIf sYear < eYear Then iMonthCount = ((Abs(sYear - eYear)) * 12) - iMonthCount End If ElseIf eMonth sMonth Then iMonthCount = Abs(eMonth - sMonth) If sYear eYear Then iMonthCount = ((sYear - eYear) * 12) - iMonthCount ElseIf sYear < eYear Then iMonthCount = ((Abs(sYear - eYear)) * 12) + iMonthCount End If End If Months = iMonthCount End Function |
All times are GMT +1. The time now is 02:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com