• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

changing a date to quarters

southwoman

New Member
Hi all


I hope you can help me with this question


I have the following date convention:


31 Mar 13


but I need to create two formulas that will firstly:


create the YEAR in this FORMAT: 2012.13


and then the quarter TO BE Q4


or alternately 1 formula to create 2012.13 Q4


Any ideas will be immensely appreciated


Regards


Nicole
 
Hi Nicole ,


Try this :


=TEXT(IF(A1>DATE(YEAR(A1),4,1),YEAR(A1),YEAR(A1)-1),"0000")&"."&TEXT(MOD(IF(A1>DATE(YEAR(A1),4,1),YEAR(A1)+1,YEAR(A1)),100),"00")&" Q"&MOD(INT((MONTH(A1)-1)/3)+3,4)+1


A1 contains a date in date format.


Narayan
 
Hi Nicole,


For the yyyy.yy


(if date is in A1)


=IF(MONTH(A1)<4,YEAR(A1)-1&"."&RIGHT(YEAR(A1),2),YEAR(A1)&"."&RIGHT(YEAR(A1)+1,2))


Quarter


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


You can combine them together


=IF(MONTH(J9)<4,YEAR(J9)-1&"."&RIGHT(YEAR(J9),2),YEAR(J9)&"."&RIGHT(YEAR(J9)+1,2)) & " Q"&LOOKUP(MONTH(J9),{0,4,7,10;4,1,2,3})
 
Back
Top