Ask Your Question
0

determine min and max dates (date/month) froma series of dates

asked 2019-08-12 22:50:33 +0200

drobble gravatar image

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 onlyC:\fakepath\Min&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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-08-13 12:00:02 +0200

updated 2019-08-13 12:03:20 +0200

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.

edit flag offensive delete link more

Comments

1

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

erAck gravatar imageerAck ( 2019-08-13 16:59:35 +0200 )edit
1

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.

m.a.riosv gravatar imagem.a.riosv ( 2019-08-13 17:03:26 +0200 )edit

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))))
Mike Kaganski gravatar imageMike Kaganski ( 2019-08-22 07:28:16 +0200 )edit
0

answered 2019-08-22 01:55:40 +0200

drobble gravatar image

Hi Mike, thanks for your tips. They are really slick. Another opportunity to learn something new (in LibreOffice). Thanks again - Fred

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-12 22:50:33 +0200

Seen: 47 times

Last updated: Aug 22