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

Finding earliest and last date in a range when condition met

K

kaushik03

Member
Hello friends,


I want to find the minimum(earliest) and maximum(latest) date from range D3:D6 for a certain product and region.


My parent data ranges from E2 to G6.


I have placed a drop down for regions( A and B) at D11, and E11 contains product number.


At B11 and C11 , I have written the formulae to get latest date and earliest date, respectively.


At B11, formula for latest date: =SUMPRODUCT(MAX($E$3:$E$6,($F$3:$F$6=$D$11)*($G$3:$G$6=$E$11)))


at C11, formula for earliest date: {=MIN(IF($F$3:$F$6=$D$11,IF($G$3:$G$6=$E$11,$E$3:$E$6,"")))}..array formula


Both the formula are working fine when my region at D11 is "A"


But when I change the region to "B", my latest date remains 12/7/2012 (which is the latest date for the region "A")....seems formula not fetching the latest date for other region though the reference cell for the region is same(D11) in the formula.I am not able to understand why the value is not changing at B11 when I change the region for same formula!!


And again, If I apply the same sumproduct formula (with MIN) to identify the earliest date when region ="A" and Product No. =133, I am getting 1/0/1900 at C11....don't understand why it happens!!


and C11 becomes 1/0/1900...however, for region B, the max and min date should be same as for this region and product number there is only one row.(In this part I know I am doing something wrong, but not able to figure out the right way)


I have uploaded the file here for further understanding of the data:

https://hotfile.com/dl/164629091/f4554de/Latest_and_earliest_date.xlsx.html


Your help is much appreciated.


Regards,

Kaushik
 
date in cell E6 is entered as text. check it using =istext(e6) and it will return the "TRUE". enter the date again and the formula will work.


Edit:


I would suggest you to use DMIN and DMAX for the purpose. I have done it in attached file.

https://www.box.com/s/fa4390ffa1c222ebe246
 
Hi Kaushik ,


I would like to go back to your formula , and point out what was wrong in it :


1. The formula itself is to be changed to :


=SUMPRODUCT(MAX(($E$3:$E$6)*(($F$3:$F$6=$D$11)*($G$3:$G$6=$E$11))))


Replace the , ( comma ) by * ( asterisk ) , and preferably , group the conditionals together.


2. You cannot replace MAX by MIN to find out the earliest ; please understand the mechanism used in the formula ; two arrays are being formed , one array of values TRUE / FALSE representing the elements = the specified region , and the other array of values TRUE / FALSE representing the elements = the specified product number. If either a region or a product number does not match the specified values , the result will be FALSE , which is equivalent to 0. MIN will return this as the minimum value , which will obviously not be the correct result. Hence the need to use the IF construct. 1/0/1900 is just 0 formatted as a date.


Narayan
 
@ shailyog,


Thank you very much for proposing the use of DMAX and DMIN for identifying the latest and earliest dates, respectively.


I have a quick 2 QS for you:


1)As you have rghtfully mentioned that my earlier for was not working as the e6 value was enterd as text. But how did you manage to change that? Because whenever I try to enter the value at e6(by deleting the earler one or draging the e5 value and then incorporate necessary changes at e6 to my suit), it again turns back to text format which is supported by =istext(e6) = true


Can u plz help me understand why it is happening?


2)If I want to stick to my last formulae, how can I manage to get the same value at B11 and C11 (as for the region "B" and product number 133, there is only one entry)? Or it is not at all possible the way I am doing it?

[Note: Though I belive DMAX and DMIN is the best approach for this problem, as you suggested]


@Narayan


As usual...very detailed, logical and in-depth explanation.....I really appreciate the way you give solution (or explanation) for any problem.


Till date, I was not aware of this logic to this extent. But now I understand completely how it works. Thank you for such wonderful explanation and help me taking to the next level.....Thank you dear...


You guys are awesome....


Regards,

Kaushik
 
Hi Kaushik ,


I think there is some confusion.


The data in the cell you are referring to is text , because Excel cannot automatically figure it out as a date.


If your native date format is mm/dd/yyyy , then data such as 13/07/2012 cannot be interpreted as a date ; if you really mean July 13 , then entering it as 7/13/2012 will enable Excel to recognize it as a valid date ; now , you can change the cell date format to dd/mm/yyyy , and the same cell ( where you entered 7/13/2012 ) will now display it as 13/07/2012 ; however , Excel now recognizes it as a valid date , and will allow all date operations on it.


One easy way to recognize the values for what they are , is :


If your numbers are always right justified , while text is left justified , then just increase the column width to a large enough value ; the numeric values will be right justified , while the text values will be left justified. Dates , which are basically numbers , will be right justified.


Narayan
 
1Q- If a cell is already text formatted and you enter date either in format 13/7/2012 or 7/13/2012 depending upon your native format, it will take both of them as text strings. you need to apply either the general format or date format to enable Excel to recognize that you are entering date.


You can check it by choosing a blank cell and then first applying TEXT format and entering date in all supported format. It will be treated as text only. now apply either general or Date format and then enter the date. Excel will recognize it as a date.


2Q- this has already been addressed by Narayan
 
Hi Yogesh ,


To add to what you have explained , if a piece of data such as 13/07/2012 is entered into a cell , Excel will not recognize it as a date if your native computer date format is mm/dd/yyyy. Please verify this.


If you enter today's date ( July 27 ) as a number 41117 , do the following :


Right click on the cell , and select the formats General , Number and Text , one after the other ; you can see the number 41117 in all of them. When you select the Date format , you wiill see 07/27/2012 or 27/07/2012 , depending on what your native computer date format is.


After entering the above numeric value , which is a valid date BTW , you can switch between the cell formats without any problem.


However , if your data entry is such that Excel cannot recognize it as a valid date ( e.g. entering 27/07/2012 when your date format is mm/dd/yyyy ) , then Excel will take it as text ; now , if you right click on the cell , and switch to the Number format , you will not see 41117 ; instead you will see 27/07/2012.


What you have explained is correct , but that is not the only explanation for a date to be taken as text.


Narayan
 
Narayan


There is not doubt about Native Date Format and I am in Full agreement with you about the fact.


By default the Excel has General Format for all cells in all worksheets. So If we enter the no. 41117 as suggested by you it will give the same result as you mentioned.

But If the cell is text formatted previously and we enter the no. or date even in the native date format (which in my case is dd/mm/yyyy- so i entered 13/7/2012) it will show it as text string only. in addition in case of no. entered as text it will have green mark on left upper corner of the cell and excel will suggest you to convert it in number format.
 
Hi Narayan and shailyog,


Sorry for asking absolutely unnecessary questions and asking you to put some efforts to answer me.


One one hand I have kept the format as m/d/yyyy and and entering the date as 13/7/2012 and want excel to read it as July 13, 2012.....It is very obvious that excel will give me an error because excel has not made to understand stupid statemnts :)


And again, what Narayan has already explained and I admit that I understood the logic but asking the same question to shailyog.....


I don't know what I was doing...probably this is because I already consumed two bottles of "TUBORG" :)


Sorry guys.....My apologies...


Wish you have a wonderful weekend ahead...


Kaushik
 
@ Narayan,


It started too early today, then spoiled with some stupid questions I asked, but I will not let it to get spoiled further as I am planning to have another two bottles of this brand, but will make sure I will not ask any more stupid questions this time to spoil your weekend...:)


Cheers!!


Kaushik
 
@SirJB7

Hi, myself!

Did I hear Tuborg? Who dares to prefer it among other Danish beers, like Carlsberg? "Probably the best beer in the world" they say, and I agree.

But being Friday and without it, why not a Tuborg? I'm in!

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