如何在excel里用vba或函数实现如问题补充所述的两个日期的月份的提取？

Question

比如2014-1-26到2014-9-25，就会输出1,2,3,4,5,6,7,8共8个月的字符；如果是跨年2013-9-13到2015.3.12就输出a9,a10,a11,a12,1,2,3,4,5,6,7,8,9,10,11,12,b1，b2共18个月的字符

Answer 1

假设两个日期数据分别在 A1 和 B1
则输出公式：
=MID("a01,a02,a03,a04,a05,a06,a07,a08,a09,a10,a11,a12,001,002,003,004,005,006,007,008,009,010,011,012,b01,b02,b03,b04,b05,b06,b07,b08,b09,b10,b11,b12",MONTH(A1)*4-3+(YEAR(B1)=YEAR(A1))*48,DATEDIF(EOMONTH(A1,-1)+1,EOMONTH(B1,0)+1,"M")*4)

如果希望得到提问中的字符格式，则：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID("a01,a02,a03,a04,a05,a06,a07,a08,a09,a10,a11,a12,001,002,003,004,005,006,007,008,009,010,011,012,b01,b02,b03,b04,b05,b06,b07,b08,b09,b10,b11,b12",MONTH(A1)*4-3+(YEAR(B1)=YEAR(A1))*48,DATEDIF(EOMONTH(A1,-1)+1,EOMONTH(B1,0)+1,"M")*4),",0",","),",0",","),"a0","a"),"b0","b")追问

比如2014-1-26到2014-3-25有两个月所以我需要显示1,2或2,3而不是1,2,3，如果是2014-1-1到2014-3-31就有3个月就可以显示1,2，3

追答

那是我理解错了，反而搞复杂了，请试试这个公式：
=MID("a01,a02,a03,a04,a05,a06,a07,a08,a09,a10,a11,a12,001,002,003,004,005,006,007,008,009,010,011,012,b01,b02,b03,b04,b05,b06,b07,b08,b09,b10,b11,b12",MONTH(A1)*4-3+(YEAR(B1)=YEAR(A1))*48,DATEDIF(A1, B1,"M")*4)

Answer 2

Sub 日期()
d1 = Format([a1], "yy")
d2 = Format([a2], "yy")
dd1 = CInt(Format([a2], "mm"))
dd2 = CInt(Format([a1], "mm"))
h = 1
Dim arr(1 To 24)
If d1 = d2 Then
For i = dd2 To dd1
arr(h) = i
h = h + 1
Next
ElseIf d2 > d1 Then
For i = dd2 To 12
arr(h) = "a" & i
h = h + 1
Next
For i = 1 To dd1
arr(h) = "b" & i
h = h + 1
Next
End If

arr1 = Join(arr)
arr1 = Application.Trim(arr1)
brr = Split(arr1)
arr1 = Join(brr, ",")
[B1] = arr1
End Sub

‘’‘’‘’‘’A1单元格为较小的日期，A2为较大的日期。输入在B1。A1,A2只考虑为日期类型时否则出错。另外A2最多比A1大一年（你题目指定的）追问

很完美，另外当有两列数据时A列为较小的日期，B列为较大日期，如何批量输出呢？还有如何不跨年的时候输出2，3,4,5,6,7，8,9呢？跨年时输出a11,a12,1,2,3,4,5,6,7,8,9,10,11,12,b1，b2，b3呢？

追答

Sub 日期()
For i = 1 To [a65536].End(xlUp).Row
d1 = Format(Cells(i, 1), "yy")
d2 = Format(Cells(i, 2), "yy")
dd1 = CInt(Format(Cells(i, 2), "mm"))
dd2 = CInt(Format(Cells(i, 1), "mm"))
h = 1
ReDim arr(1 To 24)
If d1 = d2 Then
For x = dd2 To dd1
arr(h) = x
h = h + x
Next x
ElseIf d2 > d1 Then
For x = dd2 To 12
arr(h) = "a" & x
h = h + 1
Next x
For x = 1 To dd1
arr(h) = "b" & x
h = h + 1
Next x
End If

arr1 = Join(arr)
arr1 = Application.Trim(arr1)
brr = Split(arr1)
arr1 = Join(brr, ",")
Cells(i, 3) = arr1
Next i
End Sub

追问

很完美，但是比如2014-1-26到2014-3-25有两个月所以我需要显示1,2或2,3而不是1,2,3，如果是2014-1-1到2014-3-31就有3个月就可以显示1,2，3

追答

Sub 日期()
For i = 1 To [a65536].End(xlUp).Row
y1 = Format(Cells(i, 1), "yy")
y2 = Format(Cells(i, 2), "yy")
m1 = CInt(Format(Cells(i, 1), "mm"))
m2 = CInt(Format(Cells(i, 2), "mm"))
d1 = CInt(Format(Cells(i, 1), "dd"))
d2 = CInt(Format(Cells(i, 2), "dd"))
If d2 y1 Then
For x = m1 To 12
arr(h) = "a" & x
h = h + 1
Next x
For x = 1 To m2
arr(h) = "b" & x
h = h + 1
Next x
End If

arr1 = Join(arr)
arr1 = Application.Trim(arr1)
brr = Split(arr1)
arr1 = Join(brr, ",")
Cells(i, 3) = arr1
Next i
End Sub

本回答被提问者采纳