Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 22 Aug 2019 07:28:16 +0200determine min and max dates (date/month) from a series of dateshttps://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/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 only[C:\fakepath\Min&max dates.ods](/upfiles/15656429372855606.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
Mon, 12 Aug 2019 22:50:33 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/Answer by Mike Kaganski for <p>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 only<a href="/upfiles/15656429372855606.ods">C:\fakepath\Min&max dates.ods</a>) over all years in each column.</p>
<p>See the attached sample spreadsheet.</p>
<p>The red-colored cells are manually edited to show the expected results.</p>
<p>Thanks in advance
Fred</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=204476#post-id-204476You 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.Tue, 13 Aug 2019 12:00:02 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=204476#post-id-204476Comment by Mike Kaganski for <p>You may obtain the <em>encoded</em> earliest last spring frost using this:</p>
<pre><code>=SUMPRODUCT(MIN(MONTH(B2:B9)*100+DAY(B2:B9)))
</code></pre>
<p>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 <em>date</em> 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:</p>
<pre><code>=DATE(0;QUOTIENT(B11;100);MOD(B11;100))
</code></pre>
<p>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.</p>
<p>Or you could do it in one pass, putting this into B11:</p>
<pre><code>=SUMPRODUCT(MIN(DATE(0;MONTH(B2:B9);DAY(B2:B9))))
</code></pre>
<p>... which you again need to format as date shown without years.</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=205686#post-id-205686A 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))))Thu, 22 Aug 2019 07:28:16 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=205686#post-id-205686Comment by m.a.riosv for <p>You may obtain the <em>encoded</em> earliest last spring frost using this:</p>
<pre><code>=SUMPRODUCT(MIN(MONTH(B2:B9)*100+DAY(B2:B9)))
</code></pre>
<p>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 <em>date</em> 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:</p>
<pre><code>=DATE(0;QUOTIENT(B11;100);MOD(B11;100))
</code></pre>
<p>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.</p>
<p>Or you could do it in one pass, putting this into B11:</p>
<pre><code>=SUMPRODUCT(MIN(DATE(0;MONTH(B2:B9);DAY(B2:B9))))
</code></pre>
<p>... which you again need to format as date shown without years.</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204512#post-id-204512One 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.Tue, 13 Aug 2019 17:03:26 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204512#post-id-204512Comment by erAck for <p>You may obtain the <em>encoded</em> earliest last spring frost using this:</p>
<pre><code>=SUMPRODUCT(MIN(MONTH(B2:B9)*100+DAY(B2:B9)))
</code></pre>
<p>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 <em>date</em> 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:</p>
<pre><code>=DATE(0;QUOTIENT(B11;100);MOD(B11;100))
</code></pre>
<p>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.</p>
<p>Or you could do it in one pass, putting this into B11:</p>
<pre><code>=SUMPRODUCT(MIN(DATE(0;MONTH(B2:B9);DAY(B2:B9))))
</code></pre>
<p>... which you again need to format as date shown without years.</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204510#post-id-204510Caveat, 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`Tue, 13 Aug 2019 16:59:35 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204510#post-id-204510Answer by drobble for <p>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 only<a href="/upfiles/15656429372855606.ods">C:\fakepath\Min&max dates.ods</a>) over all years in each column.</p>
<p>See the attached sample spreadsheet.</p>
<p>The red-colored cells are manually edited to show the expected results.</p>
<p>Thanks in advance
Fred</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=205677#post-id-205677Hi Mike, thanks for your tips. They are really slick. Another opportunity to learn something new (in LibreOffice).
Thanks again - FredThu, 22 Aug 2019 01:55:40 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=205677#post-id-205677Answer by Opaque for <p>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 only<a href="/upfiles/15656429372855606.ods">C:\fakepath\Min&max dates.ods</a>) over all years in each column.</p>
<p>See the attached sample spreadsheet.</p>
<p>The red-colored cells are manually edited to show the expected results.</p>
<p>Thanks in advance
Fred</p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=204472#post-id-204472Hello
1. Your red-colored cells in your example sheet **don't** contain min & max dates
2. You should have removed the reference to `smb://...` external data.
3. Please use the following formulas
Column B: earliest: `=MIN(B2:B9)`
Column B: latest: `=MAX(B2:B9)`
Column C: earliest: `=MIN(C2:C9)`
Column C: latest: `=MAX(C2:C9)`
You might ask *Why does this work?*. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions `MIN` and `MAX` just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.
See the file:
[C:\fakepath\MinMaxOfDateRanges.ods](/upfiles/15656886295249993.ods)
*If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.*Tue, 13 Aug 2019 11:34:00 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?answer=204472#post-id-204472Comment by Mike Kaganski for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204478#post-id-204478... but you can read it in the attachment ;-)
> some kind of a reverse engineering of OPs intienion
No, I just read the attachment's column headers :-)Tue, 13 Aug 2019 12:06:03 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204478#post-id-204478Comment by Mike Kaganski for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204473#post-id-204473OP wants to find the earliest/latest of all last spring frost/first fall frost; so what OP shown in the sample *is* correctTue, 13 Aug 2019 11:44:51 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204473#post-id-204473Comment by Opaque for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204480#post-id-204480Ok, your interpretation - but my conclusion of this was: "Hmm - strange column headers, since some dates are not typical dates in spring" - neglecteing the fact that there a various definitions of "start dates" and "end dates" of the seasons of a year (meteorologic, astronomical).Tue, 13 Aug 2019 12:14:39 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204480#post-id-204480Comment by Mike Kaganski for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204481#post-id-204481By the way: you are partially right, because latest of all years first fall frost was in Oct 16th, not Oct 10th ;-) - so yes, some cells don't contain max dates :-DTue, 13 Aug 2019 12:17:23 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204481#post-id-204481Comment by Opaque for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204477#post-id-204477> I would like to determine which is the min-date and max-date ...
I cannot read the word **spring** from the question. And your comment is some kind of a *reverse engineering of OPs intienion and "What might be the correct question, if the red-colours are assumed to be correct?"* :-).
But you are right - that might be the real question.
@drobble - please clarify.Tue, 13 Aug 2019 12:01:00 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204477#post-id-204477Comment by Mike Kaganski for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204483#post-id-204483> some dates are not typical dates in spring
Oh! I see some cultural difference here that I didn't know about. Here in Russia, March, April and May are spring months, and September,October and November are autumn ones; so every date in those months doesn't raise eyebrows when called "spring date" or "autumn date" ... I wasn't aware that it isn't "universal" (other than possible northern/southern hemisphere differences). Interesting!Tue, 13 Aug 2019 12:27:24 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204483#post-id-204483Comment by Opaque for <p>Hello</p>
<ol>
<li>Your red-colored cells in your example sheet <strong>don't</strong> contain min & max dates </li>
<li>You should have removed the reference to <code>smb://...</code> external data.</li>
<li>Please use the following formulas <br>
Column B: earliest: <code>=MIN(B2:B9)</code> <br>
Column B: latest: <code>=MAX(B2:B9)</code> <br>
Column C: earliest: <code>=MIN(C2:C9)</code> <br>
Column C: latest: <code>=MAX(C2:C9)</code></li>
</ol>
<p>You might ask <em>Why does this work?</em>. Date (and times) are internally stored as numbers. Date is an integer and counts the number of days since 1899-12-30 (Time is a fraction of a day) and therefore math functions <code>MIN</code> and <code>MAX</code> just simply need to evaluate the smallest and largest number. The rest (i.e. formatting) is representation of that number into a date.</p>
<p>See the file:
<a href="/upfiles/15656886295249993.ods">C:\fakepath\MinMaxOfDateRanges.ods</a> </p>
<p><em>If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204485#post-id-204485:-) Looks like you follow the meteorologic definition of seasons, which in Germany is a considered a *scientific* defintion of lazy meteorologist (and is explained every year several times to the public by tv weathermen that this definition is for the sake of simplification of date calculations (sic! - the circle is complete))Tue, 13 Aug 2019 12:38:09 +0200https://ask.libreoffice.org/en/question/204431/determine-min-and-max-dates-datemonth-from-a-series-of-dates/?comment=204485#post-id-204485