Results 1 to 9 of 9

Change date to quarter

This is a discussion on Change date to quarter within the Excel Questions forums, part of the Question Forums category; I'm looking for an efficient way to change a column of dates to a corresponding quarter. The column looks like: ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    23

    Default Change date to quarter

    I'm looking for an efficient way to change a column of dates to a corresponding quarter.

    The column looks like:

    10/10/2008
    1/10/2006
    7/10/2007
    5/10/2006
    6/10/2008
    4/10/2006
    11/10/2007
    3/10/2006


    I need it change to:
    2008-Q4
    2006-Q1
    2007-Q3
    2006-Q2
    2008-Q2
    2006-Q2
    2007-Q4
    2006-Q1

    I tried Find/Replace but when I search for example 1/*/2006 and replace it with "2006-Q1", not only are January dates replaced but also November.

    I'm not committed to using the "2006-Q1" format I just need someway to signify the year and quarter. Thank you very much for your help.

    Joe

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,834

    Default Re: Change date to quarter

    Try

    =YEAR(A1) & "-"&LOOKUP(MONTH(A1),{1,4,7,10},{"Q1","Q2","Q3","Q4"})

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    4,644

    Default Re: Change date to quarter

    Hello,

    try

    =YEAR(A1)&IF(MONTH(A1)<4,"-Q1",IF(MONTH(A1)<7,"-Q2",IF(MONTH(A1)<10,"-Q3","-Q4")))
    -------------------------
    Hope this is helpful.
    -------------------------
    only a drafter,
    but broadening my Excel knowledge.

  4. #4
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,445

    Default Re: Change date to quarter

    How about;

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

    EDIT: Beaten to it
    Regards

    Mikey B

  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,882

    Default Re: Change date to quarter

    =year(a1)&"-Q"&-int(-month(a1)/3)
    Office 2007/2010

  6. #6
    Board Regular
    Join Date
    Sep 2007
    Location
    India
    Posts
    122

    Default Re: Change date to quarter

    Try the following:

    Column A contains the dates u want to convert into quarters


    Column B : =YEAR(A1)
    COlumn C: =IF(MONTH(A1)<4,"Q1",IF(MONTH(A1)<7,"Q2",IF(MONTH(A1)<10,"Q3","Q4")))
    Column D: =B1&"-"&C1


    You can change the appearecnec of your result in column D

    Hope it helps

  7. #7
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985

    Default Re: Change date to quarter

    Also:
    Code:
    =YEAR(A1)&" - Q"&INT((MONTH(A1)+2)/3)
    Looks like everybody had a Quarter formula...
    John

  8. #8
    New Member
    Join Date
    Aug 2008
    Posts
    23

    Default Re: Change date to quarter

    Works perfect. You folks are amazing. Thanks!

  9. #9
    Board Regular
    Join Date
    May 2005
    Location
    LOndon
    Posts
    83

    Default Re: Change date to quarter

    I just used this too!!! thnx!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com