MACRO CODE TO KNOW MONTH and QUATER

Hi,

I need a formula or Macro code to get period against Month and Quarter.
In the period first number "1" is common(we can ignore) and next two numbers are year and last tow numbers are month which is starting from Apr to end on Mar.

if the is period 10910 then Month will be APr-09 and Q Q1

For your understanding i have mentioned period against Month and Quarter


PERIOD Month Q
10901 Apr-09 Q1
11106 Sep-11 Q2
11307 Oct-13 Q3
11308 Nov-13 Q3
11301 Apr-13 Q1
11302 May-13 Q1
11303 Jun-13 Q1
11304 Jul-13 Q2
11305 Aug-13 Q2
11306 Sep-13 Q2
11307 Oct-13 Q3
11308 Nov-13 Q3
11309 Dec-13 Q3

Kindly help.

Regards

Prabhu

Attached. Built a table to define the Quarters. Then using Vlookup and Concatenation, I am able to replicate your required solution.

Alan

Hi Hope this will be fine for you.


=TEXT(RIGHT(A1,2)&"-"&MID(A1,2,2),"mmm-yy")&"-"&IF(AND(VALUE(RIGHT(A1,1))>0,VALUE(RIGHT(A1,1))<= 3),"Q1",IF(AND(VALUE(RIGHT(A1,1))>3,VALUE(RIGHT(A1 ,1))<=6),"Q2","Q3"))

Regards
Prince

Here's how I'd do it in 3 steps:

1) Convert the code to an actual date:

PHP Code:

---

=DATEVALUE("01/" & CHOOSE(MID(A1,4,2),4,5,6,7,8,9,10,11,12,1,2,3) & "/" & MID(A1,2,2)) 


---

Where the above is entered into cell B1 and cell A1 contains the period code.

2) Format the date as "MMM-YY":

PHP Code:

---

=TEXT(B1,"MMM-YY") 


---

3) Determine the quarter (there are lots of ways to do this, this was the first that came to my head):

PHP Code:

---

="Q" & LOOKUP(MONTH(B1),{1,4,7,10},{4,1,2,3}) 


---

Hope this is useful.

Dex

="Q"&ROUNDUP(RIGHT(A2,2)/3,0)