vba 两个日期之间的月数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23744895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Number of months between 2 dates
提问by user3653819
I am trying to create a formula or VBA function which calculates the number of months between two dates, according to this rule: if the start date is the 15th of the month or before, or the end date is after the 15th of the month, then that month counts.
我正在尝试创建一个公式或 VBA 函数，根据此规则计算两个日期之间的月数：如果开始日期是该月的 15 号或之前，或者结束日期是该月的 15 号之后，那么那个月很重要。
For example:
例如：
Start Date End Date Output
---------- --------- --------
1/5/2014 2/16/2014 2 months
1/17/2014 2/16/2014 1 month
1/16/2014 2/5/2014 0 months
I have already tried =DATEDIF(A2, B2, "M") + IF( DATEDIF(A2, B2, "MD")>=15, 1, 0)but this only adds a month if the distance between the days in 2 dates is over 15. For example if the start date is 5/14/13-8/16/13 it will say that there are 3 months in between these dates. However, the actual distance between these 2 dates should be 4 months according to the conditions that I specified above.
我已经尝试过，=DATEDIF(A2, B2, "M") + IF( DATEDIF(A2, B2, "MD")>=15, 1, 0)但是如果 2 个日期中的天数之间的距离超过 15，这只会增加一个月。例如，如果开始日期是 5/14/13-8/16/13，它会说有 3 个月在这些日期之间。但是，根据我上面指定的条件，这两个日期之间的实际距离应该是 4 个月。
Ho do I fix this formula?
我该如何修正这个公式？
采纳答案by Tim Williams
EDit: account for years...
编辑：帐户多年...
=( (YEAR(B1)*12+MONTH(B1)) - (YEAR(A1)*12+MONTH(A1)) )
+ ( IF(DAY(A1)<=15,1,0)+IF(DAY(B1)>15,1,0) )
回答by engineersmnky
Here is a vba solution as well
这也是一个 vba 解决方案
Function date_diff_to_months(date1 As Date, date2 As Date)
Dim y1 As Integer
Dim y2 As Integer
Dim d1 As Integer
Dim d2 As Integer
Dim m1 As Integer
Dim m2 As Integer
Dim m_diff As Integer
Dim y_diff As Integer
Dim month_adjustment As Integer
y1 = Year(date1)
y2 = Year(date2)
m1 = Month(date1)
m2 = Month(date2)
d1 = Day(date1)
d2 = Day(date2)
m_diff = m2 - m1
y_diff = (y2 - y1) * 12
If (m_diff > 0 Or y_diff > 0) Then
If (d1 <= 15 And d2 >= 15) Then
month_adjustment = 1
ElseIf (d1 >= 15 And d2 <= 15) Then
month_adjustment = -1
End If
End If
date_diff_to_months = m_diff + y_diff + month_adjustment
End Function
