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

[Rick Rothstein]

Okay, I guess that is a fair way to measure the time difference. My guess is the actual percentage difference between the two functions is probably larger than your numbers show because I would guess there is some "fixed" interface time between the VB world and the worksheet world that both formula assigments experience equally... subtracting that fixed time, whatever it is, from your measured time would yield the actual time to calculate the formulas themselves... the time difference you measured would not change, but the base against which it is measured would decrease making the percentage difference increase. Of course, there is no way to measure that fixed VB to worksheet interface time, so your method is in and of itself is a more than sufficient measuring tool. Thanks for coming up with it and for running the trials using it.