To find the quarter months March, June, September, December from a given date, use
=TEXT(SMALL(({3,6,9,12})*29,CEILING(MONTH(A1)/3,1)),"mmmm")
Where A1 contains your date
Printable View
To find the quarter months March, June, September, December from a given date, use
=TEXT(SMALL(({3,6,9,12})*29,CEILING(MONTH(A1)/3,1)),"mmmm")
Where A1 contains your date
another one ..
=DATEVALUE(LOOKUP(MONTH(A1),{1,4,7,10},{3,6,9,12}) &"/1")
and format the cell
or
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10},{3,6,9,12})&"/1","mmmm")
Nice one with the "/1". An information for posterity: For some non-English languages, you'll always get a January as result. So with a slight modification, the safest bet would be to use a multiplier 29.
Instead of concatenating "/1", couldn't you concatenate "/13" so that it could never be mistaken for a month number no matter the locale?Quote:
Originally Posted by Excel Fox
Good point, Rick.Quote:
Originally Posted by Rick Rothstein
How about +2 with MONTH,
=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})*29,"mmmm")
Nice one Haseeb. Yes that should work too.
Or, combining your approach with Admin's idea, even this...Quote:
Originally Posted by Haseeb Avarakkan
=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13","mmmm")
Yes Rick, possible.
Another one with COUPNCD
=COUPNCD(A1,DATE(YEAR(A1),12,31),4,1)
format the cell.
Okay, if we are willing to let the Cell Format provide the month name (for display purposes only, I guess), then we can reduce the function down to only two function calls (the less function calls, the more efficient, right?)...Quote:
Originally Posted by Haseeb Avarakkan
=1*(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13")
and format the cell.
Firstly, I hope I am not making the thread complicated.
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 :).Quote:
...(the less function calls, the more efficient, right?)...
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
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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?Quote:
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.:confused: 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.:dontknow:Quote:
Originally Posted by Haseeb Avarakkan
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
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.