我试图列出两个给定月份之间的日期:a(2021年1月1日;b( 2021年6月1日,格式:01.2021; 02.2021; 03.2021; 04.2021; 05.2021; 06.2021
我能够找到并使用此UDF:
Function MONTHRANGE(startDate As Date, endDate As Date, _
Optional Delim As String = "; ", _
Optional dFormat As String = "MM.YYYY") As String
MONTHRANGE = Join(Evaluate("TRANSPOSE(TEXT(ROW(" & CLng(startDate) & ":" & CLng(endDate) & ")," & Chr(34) & dFormat & Chr(34) & "))"), Delim)
End Function
它的输出是以我想要的格式重复日期(一个月中的每一天(——我如何才能只返回唯一的值(每月一个(?
下面这样的操作可以完成任务:
Option Explicit
Private Sub Test()
Debug.Print GetMonths(CDate("1/1/2021"), CDate("6/1/2021"))
End Sub
Private Function GetMonths(ByVal StartDate As Date, ByVal EndDate As Date) As String
Do While StartDate <= EndDate
GetMonths = GetMonths & Format(Month(StartDate), "00") & "." & Year(StartDate) & "; "
StartDate = DateAdd("m", 1, StartDate)
Loop
GetMonths = Left(GetMonths, Len(GetMonths) - 2)
End Function