Finding the Minimun Date in a Given set of Dates in a Running Series

all4excel

Active Member
Joined
Feb 15, 2008
Messages
435
Dear Board,

I have a Column which contains dates serially starting from any date of the month and ending at any date but all these dates would be in chronolgical order..

Example:
Lets say the dates are stored in the Column B and they start from 12-Apr-2013 till 12-May-2013..then I want some formula in the adjoining column Column C which shows the Date 12_apr-2003 or the address of the first occurence of the most smallest date in the COlumn B...

So lets say Cell B2= 12-Apr-2013 till Cell B3 = 13-Apr-2013 so on so forth till Cell B32

Cell B212-Apr-13B2
Cell B313-Apr-13B2
Cell B414-Apr-13B2
Cell B515-Apr-13B2
Cell B616-Apr-13B2
Cell B717-Apr-13B2
Cell B818-Apr-13B2
Cell B919-Apr-13B2
Cell B1020-Apr-13B2
Cell B1121-Apr-13B2
Cell B1222-Apr-13B2
Cell B1323-Apr-13B2
Cell B1424-Apr-13B2
Cell B1525-Apr-13B2
Cell B1626-Apr-13B2
Cell B1727-Apr-13B2
Cell B1828-Apr-13B2
Cell B1929-Apr-13B2
Cell B2030-Apr-13B2
Cell B2101-May-13B21
Cell B2202-May-13B21
Cell B2303-May-13B21
Cell B2404-May-13B21
Cell B2505-May-13B21
Cell B2606-May-13B21
Cell B2707-May-13B21
Cell B2808-May-13B21
Cell B2909-May-13B21
Cell B3010-May-13B21
Cell B3111-May-13B21
Cell B3212-May-13B21

<colgroup><col><col><col></colgroup><tbody>
</tbody>


So I need to get a formula which will give me the address of the cell containing the most smallest date in the recurring month for that year I need the formula for RUNNING values..
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
This seems to be doing the trick

="B"&MATCH(MONTH(B2);MONTH(B$1:B$500);0)
(if B will always be the column)

or

=ADDRESS(MATCH(MONTH(B2);MONTH(B$1:B$500);0);COLUMN(B2))
(if the column needs to be determined)


enter it as an array formula. Ctrl+Shift+Enter.


you may use MONTH(B:B) instead of MONTH(B$1:B$500), but using references to a complete column in an array formula is always a bad idea, so is better to use a predefined number of rows.


This works for dates in the form dd/mm/yyyy (12/04/2013)...
 
Upvote 0
Thanks a lot Jeffrey..

I tried replying earlier too but due to some net issues my replies were not getting posted...

That solution was very useful and it was just very apt...

Can u please explain the formula though...

Warm Regards
all4excel
 
Upvote 0
Sure,

="B"&MATCH(MONTH(B2);MONTH(B$1:B$500);0)

Match(B2) will return the value of the month
Match(B$1:B$500) will return an array of all the months

Using the match function the formula will look for the month value in the month array; but there are a couple of tricks, first the "match type" argument is set to "0" because you want the formula to return the first exactly match that is found (see Excel help for other types of match); and the second trick, if you notice the "Match(B$1:B:$500)" starts at B$1 although you data starts at B$2, this is because the match function will return the relative position of the item found in the array; so if you use B$2:B:$500 the match for the first value will return "1" but you need it to be "2", so using B$1:B:$500 will do the trick. The "B"&match... part will concatenate the number return by the match function with the letter "B" giving you what you need.


=ADDRESS(MATCH(MONTH(B2);MONTH(B$1:B$500);0);COLUMN(B2))

The second function is the same, but uses the "Column" function to determine the number of the column where the data is located, and then uses the "address" function to convert it to a letter and merge it with the number found by the match function, thus given you the "B2" type of result that you need.



Also notice that this approach works because your data is in chronological order.
 
Last edited:
Upvote 0
Thanks a lot JEff for the explanation I would be revising it again and again though i have used the Match function earlier never thought that this was possible...

Since you also mentioned in the last part that the data was in chronological order., If it weren't then would it need a different approach ?

Thanks in advance..
 
Upvote 0
One warning first, please be advice that if the first row or any other row in the B$1:B$500 range is blank (not counting the last cells that are blank, just the B1 or the ones between the actual data) you'll get wrong values because the formula month("blankcell")=1 (just learned that); so if a blank cell is before any other january value all january values will return the address of the blank cell; if the blank cell is after any other january value then the blank cell will return the address for the first january value.

="B"&MATCH(IF(ISNUMBER(B2);MONTH(B2);"NA1");IF(ISNUMBER(B$1:B$500);MONTH(B$1:B$500);"NA2");0)

that formula will resolve that problem; now blank cells or cells with text will return the #N/A error...

=IFNA("B"&MATCH(IF(ISNUMBER(B2);MONTH(B2);"NA1");IF(ISNUMBER(B$1:B$500);MONTH(B$1:B$500);"NA2");0);"")

use this other if instead of the #N/A error you want it to return the text string "" (blank).

Explanation: now the value to be matched is determined by an if function that checks if the cell is a number, if it is a number then returns the month, if not then return the text "NA1"... now the array of months is determined in the same way of the match value, if it is a number then adds the month to the array, if not then it adds "NA2"; this way only actual number will find a match... notice that using "NA1" in one and "NA2" in the other will prevent non number cell from finding a match.

-------------------

Warning two... notice that this approach only checks for the month, so if you have dates between different years, you'll need this other formula:

="B"&MATCH(IF(ISNUMBER(B2);MONTH(B2)&YEAR(B2);"NA1");IF(ISNUMBER(B$1:B$500);MONTH(B$1:B$500)&YEAR(B$1:B$500);"NA2");0)

this one checks the month and year...

Explanation: The formula is the same as the ones above, but instead of just looking for (value to be matched) and looking in (array) for the month, now it checks for a text string consisting of "monthyear" (for example if the B cell is 01/08/2013 the formula will search for 82013, thus only finding other "82013" (august 2013) values)...


------------------

Now regarding your question:

Yes, quoting you "the first occurence of the most smallest date"; if the data isn't in chronological order then the formula will return the first value found for the month.

This will work if the data isn't in cronological orden

=MIN(IF(MONTH(B2)=MONTH($B$1:$B$500);$B$1:$B$500;"NA3"))

This will find the first date (minimum, thus the min function) for each month (for the array containing the dates of the month)... (Im only posting this so you can see the evolution of the actual final formula...) This has two problem, one, if the range "$B$1:$B$500" has any text then they all return a #VALUE! error... including "B1" (the header, I guess)... and if there is any blank then the month will return "0" and will ended up in a "00/01/1900" date...


=MIN(IF(IF(ISNUMBER(B1);MONTH(B1);"NA1")=IF(ISNUMBER($B$1:$B$500);MONTH($B$1:$B$500);"NA2");$B$1:$B$500;"NA3"))

This will find the first date for each month... without the text (#VALUE!) error, but still giving the "0" bad date...

This next step is irrelevant for you, since you want the address and not the actual date; if you want the actual date then using this:

=IF(MIN(IF(IF(ISNUMBER(B1);MONTH(B1);"NA1")=IF(ISNUMBER($B$1:$B$500);MONTH($B$1:$B$500);"NA2");$B$1:$B$500;"NA3"))>0;MIN(IF(IF(ISNUMBER(B1);MONTH(B1);"NA1")=IF(ISNUMBER($B$1:$B$500);MONTH($B$1:$B$500);"NA2");$B$1:$B$500;"NA3"));"")

Will solve the "0" problem... is just an if function checking if the result is bigger than "0" if yes then returns the result, if not then returns "" (blank)...

Now back to your actual problem..
That last step is not needed for you since "0" doesn't (or at least shouldn't exist in your "$B$1:$B$500" range)... so all that is needed now is match...

="B"&MATCH("last formula";$B$1:$B$500;0)

so your final formula would be:

="B"&MATCH(MIN(IF(IF(ISNUMBER(B2);MONTH(B2);"NA1")=IF(ISNUMBER($B$1:$B$500);MONTH($B$1:$B$500);"NA2");$B$1:$B$500;"NA3"));$B$1:$B$500;0)

again the text and blanks will return the #N/A error, if needed, just
=IFNA("formula above";"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top