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

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

minimum value from minium or first year

G

griffin

New Member
Hi Experts,


I tried before and got the partial solution of finding the minimum month for a particular project but now i have one more condition where i need to find minimum month out of minimum value year.

My first month starts with Oct and ends in Sep

Last time i used the array formula(i vlookuped with my own custom array where 1=oct,2=nov.......12=Sep) like this min{(if(A:A=A2,C:C,""))} which was giving Oct as first month for this project but actually it started in 2012 with JUN as start or min date.

[pre]
Code: 
A        B       C
Project	Year	Month
109202	2012	AUG
109202	2013	DEC
109202	2012	JUN
109202	2013	NOV
109202	2013	OCT
109202	2012	JUL
109202	2012	SEP
109202	2013	JAN
109202	2013	APR
109202	2013	MAY
109202	2013	MAR
109202	2013	FEB
[/pre]
Please help me on this.


Thanks in advance!
 
Hi griffin,


Can you please check the below formula...


=TEXT(MIN(IF($A$1:$A$12=109202,DATEVALUE(1&$C$1:$C$12&$B$1:$B$12))),"mmm-yyyy")


Confirm the formula by Ctrl Shift Enter.. Not just Enter..


Regards,

Deb
 
Hi griffin,


Please see this file:


http://dl.dropbox.com/u/60644346/Min%20Date%20%26%20Year.xlsx


or try:


Code: 
=MIN(VALUE("01-"&C2:C13&"-"&B2:B13))


With Ctrl+Shift+Enter.


Regards,
 
Hi Faseeh,


The formula in your first post works great,thank you so much even it takes a lot of time to calculate but i think its a general issue with all array formulas.

Can you please make me understand this part DATEVALUE(1&$C$1:$C$12&$B$1:$B$12)))


Thanks again!
 
Hi griffin,


The part you mentioned of Deb's formula actually converts/combines 1 with the month and date converting them to the 01-XX-20XX type date, then for every date we have a date value that is the day counted from January 1, 1900. so that if the Date-value is greater then 109202, you will have the date values.


Now can try converting 109202 to date let's see what's happens...


Faseeh
 
Hi Faseeh..


Thanks for supporting..


do one more favor .. help me to understand.. what exactly this SPAMMER want to do with this forum.. ;)


Regards,

Deb
 
You must log in or register to reply here.
Back
Top