1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Dynamically picks earliest date (in quarter)

Discussion in 'Ask an Excel Question' started by Khalil Lakhani, Jun 19, 2017.

  1. Khalil Lakhani

    Khalil Lakhani New Member

    Messages:
    26
    Hello Everyone!
    I am converting date column to quarter output. On another sheet, I have a strat that should automatically picks earliest of quarters from the above list.
    Attached is the spreadsheet that visually explains - Essentially, I need a formula that can pick earliest of quarters from a column -

    Thanks in advance -
    Khalil

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    Maybe……

    =INDEX(L8:L100,MATCH(AGGREGATE(5,6,K8:K100),K8:K100,0))

    Regards
    Bosco
  3. Khalil Lakhani

    Khalil Lakhani New Member

    Messages:
    26
    It works - thanks! Now if I have to add 1 quarter to the subsequent rows - like on cell D10 and onwards - how do I do that?
  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    Maybe……

    upload_2017-6-21_2-3-34.png

    1] D9 :

    =INDEX(L8:L100,MATCH(AGGREGATE(5,6,K8:K100),K8:K100,0))

    2] D10, copy down :

    =LEFT(D9,4)+(RIGHT(D9)="4")&"-Q"&MID(2341,RIGHT(D9),1)

    Regards
    Bosco

    Attached Files:

    Last edited: Jun 19, 2017
  5. Khalil Lakhani

    Khalil Lakhani New Member

    Messages:
    26
    Works perfectly - thanks again! Quick question though - what is that '2341' in the mid formula?
  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    =MID(2341,RIGHT(D9),1)

    This portion =RIGHT(D9) >> return the upper cell last digit (1, 2, 3 or 4)

    Then,

    =MID(2341,1,1) >> return 2

    =MID(2341,2,1) >> return 3

    =MID(2341,3,1) >> return 4

    =MID(2341,4,1) >> return 1

    Regards
    Bosco
    Thomas Kuriakose likes this.
  7. Khalil Lakhani

    Khalil Lakhani New Member

    Messages:
    26
    Awesome! Thanks -

Share This Page