Ask Your Question

Revision history [back]

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.

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.