Formula Converts Date To Quarter & Year

  • Re: Formula Converts Date To Quarter & Year


    rhc,


    Or, if you prefer, you can use a custom cell format like below and eliminate the need for a formula.

    Quote

    "Q1-" yy


    Jim

  • Re: Formula Converts Date To Quarter & Year


    Quote from ByTheCringe2

    How about:


    ="Q"&INT((MONTH(A1)/4)+1)&"-"&RIGHT(YEAR(A1),2)



    Works beautifully.


    Thanks so much!

  • Re: Formula Converts Date To Quarter & Year


    Quote from ByTheCringe2

    Jim, that gives Q1-06 even for a date in September...


    True, you would need four formats. Works if you are talking column headers and such but not as well if you just have random dates you want to sort or something. Just thought I'd toss it out there.


    Jim

  • Re: Formula Converts Date To Quarter & Year


    Slight problem with the formula.


    This one works better:


    ="Q"&INT((MONTH(A1)-1)/3+1)&" - CY"&YEAR(A1)


    You can ignore my change for the 'year' part. The part I'm fixing is the Month calculation.



    Quote from rhc;307393

    Works beautifully.


    Thanks so much!

  • Re: Formula Converts Date To Quarter & Year


    Quote from rhc;307345

    A1 contains a date formatted cell as dd/mm/yr, say 01/15/06. What formula, in B1, would convert this date to: Q1-06?


    My date is in cell D2 and formatted MM/DD/YYYY.
    For the result I prefer to have the year displayed first and the quarter following so that it sorts chronologically.
    Try this =YEAR(D2)&"-Q"&ROUNDUP(MONTH(D2)/3,0)