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 LakhaniNew 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 -

Khalil

File size:
13.4 KB
Views:
4
2. ### bosco_yipWell-Known Member

Messages:
1,248
Maybe……

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

Regards
Bosco
3. ### Khalil LakhaniNew 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_yipWell-Known Member

Messages:
1,248
Maybe……

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:

• ###### Quarter example(1).xlsx
File size:
13.9 KB
Views:
2
Last edited: Jun 19, 2017
5. ### Khalil LakhaniNew Member

Messages:
26
Works perfectly - thanks again! Quick question though - what is that '2341' in the mid formula?
6. ### bosco_yipWell-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 LakhaniNew Member

Messages:
26
Awesome! Thanks -