Find Quarter Month From Date In Excel

**Haseeb A** · 04-17-2012, 05:10 AM

Firstly, I hope I am not making the thread complicated.

...(the less function calls, the more efficient, right?)...

I think should be efficient. Rick, you have better knowledge & experience in all these field, so you know the things better than a less experienced guy like me

**Rick Rothstein** · 04-18-2012, 11:04 PM

Originally Posted by Haseeb Avarakkan

I think should be efficient. Rick, you have better knowledge & experience in all these field, so you know the things better than a less experienced guy like me

.

If we just redesign the COUPNCD function, possible to get (unless I miss something) with just one function.

=COUPNCD(A1,"1/1/9999",4,1)-1

If we remove the -1 gives the 1st day in the next quarter of the date in A1

I was a Civil Engineer during my working career, so I no know nothing about financial "things". That means I am totally unfamiliar with "coupons", but the COUPNCD function may be an exception to the efficiency rule.The first time I tried your formula, I set up a column of 14 years worth of individual days and copied your formula down in the next column over... Excel seemed to lock up. So I tried it with just one year's worth of individual days... I could watch the COUPNCD function displaying its calculated values in slow-motion down the column (which is why it looked like Excel had locked up in my initial test... the function was taking "forever" to calculate that many results). I don't know if using 1/1/9999 as a date is burdening the function down with a huge number of internal calculations or if the function (being from the Analysis ToolPak, hence, not built-in) is just a slow calculating function, but it does not look like your formula is an efficient one, at least given my test results. Can you confirm the slowness of COUPNCD when used multiple times down a column of dates?

**Haseeb A** · 04-19-2012, 04:06 AM

Yes Rick. I got the slowness in Excel 2007 too (I do not have 2003). I think it is because of 1/1/9999. When I changed the formula to,

=COUPNCD(A1,"1/1/"&YEAR(A1)+1,4,1)-1

and tested with 10000 cells, calculation was normal. Also, I didn't know it is a part of Analysis ToolPak in earlier versions.

**Rick Rothstein** · 04-19-2012, 08:07 AM

Originally Posted by Haseeb Avarakkan

Yes Rick. I got the slowness in Excel 2007 too (I do not have 2003). I think it is because of 1/1/9999. When I changed the formula to,

=COUPNCD(A1,"1/1/"&YEAR(A1)+1,4,1)-1

and tested with 10000 cells, calculation was normal. Also, I didn't know it is a part of Analysis ToolPak in earlier versions.

Okay then, we are back to two function calls for each of our formulas.

Which is more efficient... I don't know. My MONTH call and your YEAR call should be equal... and both involve a single concatenation and both involve a single math operation, so they should balance out as well... so it comes down to which of LOOKUP or COUPNCD is more efficient, the answer to which I don't know.

I guess, until someone who knows how to test the effeciency of these two functions and reports back here, one cannot go too wrong simply using whichever they find more comfortable.

**Admin** · 04-19-2012, 12:23 PM

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

Code:

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** · 04-19-2012, 02:08 PM

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.