Find Quarter Month From Date In Excel

[Excel Fox]

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

[Admin]

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")

[Excel Fox]

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.

[Rick Rothstein]

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?

[Admin]

Instead of concatenating "/1", couldn't you concatenate "/13" so that it could never be mistaken for a month number no matter the locale?

Good point, Rick.

[Haseeb A]

How about +2 with MONTH,

=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})*29,"mmmm")

[Excel Fox]

Nice one Haseeb. Yes that should work too.

[Rick Rothstein]

How about +2 with MONTH,

=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})*29,"mmmm")
Or, combining your approach with Admin's idea, even this...

=TEXT(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13","mmmm")

[Haseeb A]

Yes Rick, possible.

Another one with COUPNCD

=COUPNCD(A1,DATE(YEAR(A1),12,31),4,1)

format the cell.

[Rick Rothstein]

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?)...

=1*(LOOKUP(MONTH(A1)+2,{3,6,9,12})&"/13")

and format the cell.

[Haseeb A]

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 :).

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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (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=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=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=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (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=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (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=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

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]

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]

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:

[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.