Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Formula Converts Date To Quarter & Year

  1. #1
    Join Date
    19th April 2006
    Usergroup
    Registered Users
    Posts
    19

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Usergroup
    OzMVP
    Posts
    4,898

    Re: Formula Converts Date To Quarter & Year

    How about:

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Usergroup
    QuickClip
    Posts
    966

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Usergroup
    OzMVP
    Posts
    4,898

    Re: Formula Converts Date To Quarter & Year

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th April 2006
    Usergroup
    Registered Users
    Posts
    19

    Re: Formula Converts Date To Quarter & Year

    Quote Originally Posted by ByTheCringe2
    How about:

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

    Works beautifully.

    Thanks so much!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Usergroup
    QuickClip
    Posts
    966

    Re: Formula Converts Date To Quarter & Year

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Usergroup
    OzMVP
    Posts
    4,458

    Re: Formula Converts Date To Quarter & Year

    Hi,

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

    HTH

  8. #8
    Join Date
    21st February 2012
    Usergroup
    Registered Users
    Posts
    1

    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 Originally Posted by rhc View Post
    Works beautifully.

    Thanks so much!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    17th May 2012
    Usergroup
    Registered Users
    Posts
    1

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th May 2016
    Usergroup
    Registered Users
    Posts
    1

    Re: Formula Converts Date To Quarter & Year

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Date converts to american format
    By littlelee in forum Excel General
    Replies: 5
    Last Post: September 5th, 2015, 19:32
  2. Replies: 3
    Last Post: April 5th, 2006, 16:20
  3. Update formula for year to date based on selection
    By markc in forum Excel General
    Replies: 5
    Last Post: November 16th, 2005, 20:03
  4. Replies: 3
    Last Post: December 24th, 2004, 10:33
  5. [Solved] Formulas : Fiscal Year Calculations by Quarter
    By wctoomey in forum Excel General
    Replies: 4
    Last Post: February 11th, 2004, 06:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno