Find the last date of quarter and show all another day by quarter-CodePudding

I would like to find the last date of quarter using the date on input and show all quarter for 10 years like this :

My code is :

Sub Trimestre()

 If Month(Sheets("Paramétrage").Cells(3, 3).Value) >= 1 And 
 Month(Sheets("Paramétrage").Cells(3, 
 3).Value) <= 3 Then
 Sheets("Paramétrage").Cells(12, 1).Value = "T1"
 End If


 If Month(Sheets("Paramétrage").Cells(3, 3).Value) > 3 And 
Month(Sheets("Paramétrage").Cells(3, 
3).Value) <= 6 Then
Sheets("Paramétrage").Cells(12, 1).Value = "T2"
End If


If Month(Sheets("Paramétrage").Cells(3, 3).Value) > 6 And Month(Sheets("Paramétrage").Cells(3, 
3).Value) <= 9 Then
Sheets("Paramétrage").Cells(12, 1).Value = "T3"

End If


If Month(Sheets("Paramétrage").Cells(3, 3).Value) > 9 And Month(Sheets("Paramétrage").Cells(3, 
3).Value) <= 12 Then
Sheets("Paramétrage").Cells(12, 1).Value = "T4"
End If



End Sub

I can find the first T (Quarter) but I have no idea how to find the last date of the quarter and show all the date after. Thank you for your precious help.

CodePudding user response：

This function will always give you the last day of the quarter of whatever date you feed into it.

Function LastDayOfQuarter(refDate As Date) As Date
    Dim DT As Date
    DT = WorksheetFunction.EoMonth(refDate, 0)
    Do Until Month(DT) Mod 3 = 0
        DT = Application.WorksheetFunction.EoMonth(DT, 1)
    Loop
    LastDayOfQuarter = DT
End Function

And here is the code edited to just return the quarter:

Function Quarter(refDate As Date) As String
    Dim DT As Date
    DT = WorksheetFunction.EoMonth(refDate, 0)
    Do Until Month(DT) Mod 3 = 0
        DT = Application.WorksheetFunction.EoMonth(DT, 1)
    Loop
    Quarter = "Q" & Month(DT) / 3
End Function

If you star with the function `LastDayOfQuarter in cell B5, then each cell below "=eomonth(B5,3)" then you will get a list of the next number of end dates of quarters.

You can then use the "Quarter" Function to return which quarters those are.

Example:

CodePudding user response：

Loops are not needed. Two one-liners will do for the quarter date calculations:

DateThisQuarterFirst = DateAdd("q", -1, DateSerial(Year(Date), DatePart("q", Date) * 3   1, 1))
 
DateThisQuarterLast = DateAdd("q", 0, DateSerial(Year(Date), DatePart("q", Date) * 3   1, 0))