Hi
I’m hoping someone can help me with the following problem:
I have a series of dates in two columns and I would like to determine which is the min-date and max-date (based on day & month onlyMin&max dates.ods) over all years in each column.
See the attached sample spreadsheet.
The red-colored cells are manually edited to show the expected results.
Thanks in advance
Fred
You may obtain the encoded earliest last spring frost using this:
=SUMPRODUCT(MIN(MONTH(B2:B9)*100+DAY(B2:B9)))
This will give you a number 505; it is a month number multiplied by 100 plus day number - so this reads “May 5”. Since there’s no date that doesn’t include a year, it’s impossible to encode that into a proper date; and after you have the said encoded number in, say, B11, you may put this into B14:
=DATE(0;QUOTIENT(B11;100);MOD(B11;100))
which makes use of the fact that year 0 was a leap year, so any valid combination of month and day (including Feb 29th) is representable in it. You may format B14 then as you like, to only display month and day.
Or you could do it in one pass, putting this into B11:
=SUMPRODUCT(MIN(DATE(0;MONTH(B2:B9);DAY(B2:B9))))
… which you again need to format as date shown without years.
Caveat, assuming this DATE(0;…) would yield year 0 is wrong, in fact it produces year 2000 because of the two-digit year input applied (check with =DATE(0;1;1)), but luckily year 2000 was a leap year as well 
Btw, in calendar calculations there is no year 0, but year 1 BCE instead. Check with date input 0001-1-1 in A1 and a formula =A1-1 yields -0001-12-31
One thing to take into account on using MIN() inside SUMPRODUCT() is with undesired situations like blank cells in the range generating a minimum, so adding a large value when cell is zero, helps to solve the issue =SUMPRODUCT(MIN(MONTH(B2:B10)*100+DAY(B2:B10)+(B2:B10=0)*1E+099)), MAX() in similar situations usually it’s not a problem.
A variant to use with MIN calculation: use another leap year, which gives a date with negative numerical value, like 1600. This would allow to use simple formula
=SUMPRODUCT(MIN(DATE(1600;MONTH(B2:B9);DAY(B2:B9))))Hi Mike, thanks for your tips. They are really slick. Another opportunity to learn something new (in LibreOffice).
Thanks again - Fred