Announcement

Collapse
No announcement yet.

Formula Converts Date To Quarter & Year

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formula Converts Date To Quarter & Year



    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?

  • #2
    Re: Formula Converts Date To Quarter & Year

    How about:

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

    Comment


    • #3
      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.
      "Q1-" yy
      Jim

      Comment


      • #4
        Re: Formula Converts Date To Quarter & Year

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

        Comment


        • #5
          Re: Formula Converts Date To Quarter & Year

          Originally posted by ByTheCringe2
          How about:

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

          Works beautifully.

          Thanks so much!

          Comment


          • #6
            Re: Formula Converts Date To Quarter & Year

            Originally posted by 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

            Comment


            • #7
              Re: Formula Converts Date To Quarter & Year

              Hi,

              ="Q"&LOOKUP(MONTH(A1),{1,4,7,10},{1,2,3,4})&"-"&TEXT(A1,"yy")

              HTH
              Kris

              ExcelFox

              Comment


              • #8
                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.


                Originally posted by rhc View Post
                Works beautifully.

                Thanks so much!

                Comment


                • #9
                  Re: Formula Converts Date To Quarter & Year

                  I like using roundup() to alleviate the +1/-1 stuff:

                  ="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&RIGHT(YEAR(A1),2)

                  Comment


                  • #10


                    Re: Formula Converts Date To Quarter & Year

                    Originally posted by rhc View Post
                    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)

                    Comment

                    Working...
                    X