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

#### all4excel

##### Active Member
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 B2 12-Apr-13 B2 Cell B3 13-Apr-13 B2 Cell B4 14-Apr-13 B2 Cell B5 15-Apr-13 B2 Cell B6 16-Apr-13 B2 Cell B7 17-Apr-13 B2 Cell B8 18-Apr-13 B2 Cell B9 19-Apr-13 B2 Cell B10 20-Apr-13 B2 Cell B11 21-Apr-13 B2 Cell B12 22-Apr-13 B2 Cell B13 23-Apr-13 B2 Cell B14 24-Apr-13 B2 Cell B15 25-Apr-13 B2 Cell B16 26-Apr-13 B2 Cell B17 27-Apr-13 B2 Cell B18 28-Apr-13 B2 Cell B19 29-Apr-13 B2 Cell B20 30-Apr-13 B2 Cell B21 01-May-13 B21 Cell B22 02-May-13 B21 Cell B23 03-May-13 B21 Cell B24 04-May-13 B21 Cell B25 05-May-13 B21 Cell B26 06-May-13 B21 Cell B27 07-May-13 B21 Cell B28 08-May-13 B21 Cell B29 09-May-13 B21 Cell B30 10-May-13 B21 Cell B31 11-May-13 B21 Cell B32 12-May-13 B21

<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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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

(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)...

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

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.

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:
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 ?

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

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

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:

Replies
3
Views
175
Replies
8
Views
151
Replies
1
Views
416
Replies
3
Views
173
Replies
3
Views
225

### Forum statistics

1,203,690
Messages
6,056,753
Members
444,889
Latest member
ibbara ### 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.

### Which adblocker are you using?    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

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