我的工作表看起来像这样
的数据
我能够得到一个日期范围之间的总季度,如单元格D4所示。我创建了一个UDF,它工作得很好。
Function GetDayPerQtr(ReimDt As Date, HoldDt As Date) As String
Dim Qtr As Integer
Dim QtrStDt As Date
Dim QtrEndDate As Date
Dim TotalQtrs As Long
Dim tmp As String
Dim i As Long
Qtr = DetermineQuarter(ReimDt)
QtrEndDate = DateSerial(Year(ReimDt), (Qtr * 3) + 1, 0)
If QtrEndDate <= HoldDt Then
TotalQtrs = DateDiff("q", ReimDt, HoldDt)
If TotalQtrs > 1 Then
QtrStDt = ReimDt
For i = 1 To TotalQtrs
Qtr = DetermineQuarter(QtrStDt)
If Qtr = 2 Or Qtr = 3 Then
QtrEndDate = DateSerial(Year(QtrStDt), Qtr * 3, 30)
Else
QtrEndDate = DateSerial(Year(QtrStDt), Qtr * 3, 31)
End If
tmp = tmp & ", " & (QtrEndDate - QtrStDt + 1)
QtrStDt = QtrEndDate + 1
Next i
tmp = tmp & ", " & (HoldDt - QtrStDt + 1)
tmp = Mid(tmp, 2)
Else
tmp = QtrEndDate - ReimDt
QtrStDt = QtrEndDate + 1
tmp = tmp & ", " & (HoldDt - QtrStDt + 1)
End If
Else
tmp = HoldDt - ReimDt
End If
GetDayPerQtr = tmp
End Function
Function DetermineQuarter(Dt As Date) As Integer
Dim inputDate As Date
Dim quarter As Integer
Dim inputMonth As Integer
inputDate = Dt
inputMonth = Month(inputDate)
If inputMonth >= 1 And inputMonth <= 3 Then
quarter = 1
ElseIf inputMonth >= 4 And inputMonth <= 6 Then
quarter = 2
ElseIf inputMonth >= 7 And inputMonth <= 9 Then
quarter = 3
ElseIf inputMonth >= 10 And inputMonth <= 12 Then
quarter = 4
End If
DetermineQuarter = quarter
End Function
字符串
现在我试图得到的结果显示在绿色细胞的基础上的开始日期和结束日期。
所以基本上,我在单元格D4中显示的值应该放在相应的列中。
我尝试了上述代码的各种组合,最终感到困惑。有人能帮助我或指导我如何实现我想要的吗?我对Excel和Excel公式的建议持开放态度。
我也试图在下面加入一个逻辑,但被卡住了。
'=GetTotalDaysInQtr("Q1",2020,B4,C4)
Function GetTotalDaysInQtr(Qtr As String, Yr As Long, ReimDt As Date, HoldDt As Date) As Long
End Function
型
1条答案
按热度按时间af7jpaap1#
如果你把真实的日期放在工作表上,你可以使用这样一个公式:
第一个月
的数据
创建季度日期可以使用公式以及使用
EDATE
函数来实现,开始日期为1.1.2020,月份参数为3。