Formulas of first and last day of each quarterly month.

[KyleJackMorrison]

Hello,

I'm struggling to find a formula that will show the First and Last date of each quarterly month. Basically need to have these dates but for the current year so the year will change if its 2020...

First Quarter:
01/01/2019
31/03/2019
Second Quarter
01/04/2019
30/06/2019
Third Quarter
01/07/2019
30/09/2019
Forth Quarter
01/10/2019
31/10/2019

Thanks in advance.
Kyle

 

[arsabra]

Just Put any Year in Sheet1 at A1 and Run the Module

Sub CreatQuaterDates()
'On Error Resume Next
Dim dt As Date: Dim Rng As Range
Dim QM As Long 'FirstMonthInQuarter:
Dim LQ As Long: ' LastMonthInQuarter
Dim oYear As Variant: oYear = Sheet1.Range("A1").Value
Dim YearIn As Long: YearIn = CLng(oYear):

' Validate Year Value
If YearIn = 0 Then Exit Sub
If YearIn > 9999 Or YearIn < 1900 Then Exit Sub

dt = DateSerial(YearIn, 1, 1) 'Creat initial Date of the Year
Set Rng = Sheet1.Range("A2:B13")


' Get Dates of Quaters i is the Quater Index
For i = 1 To 12 Step 3
Rng(i, 1) = "Quater " & Int(i / 3) + 1
Rng(i, 1).Offset(1, 0) = DateSerial(Year(dt), i, 1)
Rng(i, 1).Offset(2, 0) = DateSerial(Year(dt), (Int(i - 1) / 3) * 3 + 4, 0)
Rng(i, 2).Offset(1, 0) = Format(DateSerial(Year(dt), i, 1), "dddd")
Rng(i, 2).Offset(2, 0) = Format(DateSerial(Year(dt), (Int(i - 1) / 3) * 3 + 4, 0), "dddd")
Next
End Sub