Find Quarter Month From Date In Excel

[Admin]

Hi

I did a small test and the average time taken for Hasseb's and Rick's function are .22 secs and .15 secs respectively.


Here is how I did in a blank workbook

Sub Haseeb()
    Dim t
    ClearAllCells
    t = Timer
    With Range("b1:b10000")
        .FormulaR1C1 = "=COUPNCD(rc[-1],""1/1/""&YEAR(rc[-1])+1,4,1)-1"
    End With
    Debug.Print Format(Timer - t, "00.00")
End Sub
Sub Rick()
    Dim t
    ClearAllCells
    t = Timer
    With Range("b1:b10000")
        .FormulaR1C1 = "=1*(LOOKUP(MONTH(rc[-1])+2,{3,6,9,12})&""/13"")"
    End With
    Debug.Print Format(Timer - t, "00.00")
End Sub
Sub ClearAllCells()
    Range("b1:b10000").ClearContents
End Sub
Sub FillDates()
    Range("a1") = DateSerial(1950, 1, 1)
    Range("a1:a10000").DataSeries , 3, 3
End Sub