[Solved] Sum between Dates

[nophoi]

Hello guys,
as I said in another topic, I have to copy and paste a lot of data to my Calc (columns A, B, C, D, E, F).
I'm using Paste Special > Unformatted Text > Column Type: English (US) because I need commas as decimal separator on the columns C, D, E, F and I sum them in the column G.

Now I have to sum the column G (G1+G2+G3+G4+G5+G6) if the dates (column A) are between 01/05/2020 and 31/05/2020.

The problem is that these data (column A) contain dates and times.
How can I solve this?

I attached the sample file.
Thanks!

[JohnSUN-Pensioner]

You were lucky - just yesterday, to answer a very similar question, I recordedthis video. Your first column is not dates, but text. Just press CTRL + F8 to see this. Perhaps you simply did not know about this possibility of importing text into a table - you can specify exactly in what form you get the date. Just click on the column heading and select the option you need from the drop-down list.
However, if you did not do this during the import, you can do it at any time with "Text to column" (this is also in the video)

[nophoi]

Thank you! I'm learning to use the Text Import (and Text to Column).

[JohnSUN-Pensioner]

That's not all.

Rewrite your formula as

Code: Select all

=SUMIFS(G1:G6;A1:A6;">=2020-05-01";A1:A6;"< 2020-06-01")

or as

Code: Select all

=SUMPRODUCT($G$1:$G$6;YEAR($A$1:$A$6)=2020;MONTH($A$1:$A$6)=5)

[nophoi]

I tried my formula and it works.

[JohnSUN-Pensioner]

Okay, no problem!
Let's agree - when you understand that your formula is not working correctly (for example, skips amounts for May 31, June 30 and generally for any last day of the month, or even shows 0 when changing the locale), you will not ask a new question, but you will come back here, in this topic and look again at the formulas. Good?

[Villeroy]

Sum, Min, Max, Average per month for 5 categories and 7 persons in 1000 rows without a single formula: download/file.php?id=38070

[nophoi]

Okay, no problem!
Let's agree - when you understand that your formula is not working correctly (for example, skips amounts for May 31, June 30 and generally for any last day of the month, or even shows 0 when changing the locale), you will not ask a new question, but you will come back here, in this topic and look again at the formulas. Good?

Hi JohnSUN-Pensioner, can you please explain why my formula may skip the last day of the month or may show 0 when I change the locale?
Anyway, I will do some tests to be sure and I will use your formulas, if needed

Sum, Min, Max, Average per month for 5 categories and 7 persons in 1000 rows without a single formula: download/file.php?id=38070

Hi Villeroy, I see that your file use Macros. I would like to not use them. Thanks anyway!

[Zizi64]

can you please explain why my formula may skip the last day of the month or may show 0 when I change the locale?

Code: Select all

=SUMIFS(G1:G6;A1:A6;">="&"01/05/2020";A1:A6;"<="&"31/05/2020")

Because you compare a Text constant but not a Date Value with various localized formats. The condition of the Text constants will match in another cases than the numeric values will match depending on the locale of the formatted Date.
read: "B">="A" because the ascii code of the B is larger than the ascii code of the A.

Note:
A cell always contains more than one content. For example the A1 cell of your sample file contains:
- a string (the date dinamically formated according to the actual locale) : 20-05-01 09:45 (appeared in my LibreOffice 6.2.8)
- a numeric value (the elapsed days since the base date): 43952,4064699074
- a formula: there is not in this case

[JohnSUN-Pensioner]

...please explain why my formula may skip the last day of the month...

Because the condition in your formula compares the value in the cell with the date 31-05-2020. More precisely, from 31-05-2020 00:00:00, with the first second of this day. And the records in your table will contain date-time of the form 31-05-2020 14:10:27. This is quite a bit more, but still more than you specify in the upper limit of comparison BETWEEN. Therefore, these values ​​will not fall into the result. That is why the upper limit must be specified "first second of the next day" and the comparison condition is "less".
By the way, changing the format of the first column from "date-time" to "date" does not solve this problem - you will not see hours-minutes, but they will still be in the cell values.

...or may show 0 when I change the locale?

Because I saw it with my own eyes in your book on my locale.
The date of the type DD-MM-YYYY will be understood in many languages, but for example it is gibberish for Americans, they are used to MM / DD / YYYY. That is why the developers of different countries agreed among themselves and adopted a special date format YYYY-MM-DD, legitimized it as a separate standard. Now all spreadsheets and all databases know what such a record means and are not mistaken when they interpret it as a date. (I deliberately do not refer to ISO, about it wrote more than once in this forum - a quick search will yield a lot of results)

Hi Villeroy, I see that your file use Macros. I would like to not use them

Be prepared that Villeroy will answer strictly - he repeatedly explained to forum visitors that he resorted to macros only in exceptional cases (not in this case for sure!), he specifically explained that there were no macros and formulas in this book. And suddenly you say that you saw macros in his book. It's offensive.
Why did you decide that there are macros? Was there any message?
In fairness, I myself wanted to propose using the Pivot Table for your task. It is really easy and simple ... when you have certain experience and skills. I realized that you do not have these skills yet, to explain step by step what and how to do it would take too long, send reading documentation (RTFM) is impolite ... Therefore, I limited myself to the correct (as it seems to me) formula.

[nophoi]

I didn't mean to be offensive when I wrote "my formula works" because I just wanted to say that my formula it's working with those data in my sample document and I didn't know what you and Zizi64 explained in this post. And I thank you for this because I'm learning new things.
Also, I didn't mean to be offensive to Villeroy. When I opened that file, I see a message about Macros. As you know, I'm a newbie so I thought there were macros in there and I don't want to use them. That's all.

[JohnSUN-Pensioner]

When I opened that file, I see a message about Macros

Everything else is not important - no offense, this is a normal discussion on the forum.
But the macro message is a troubling symptom! The fact is that there really are no macros in that book. If the office security system issued such a warning, then something went wrong somewhere.
Please tell us in which version of Calc you opened this spreadsheet, how exactly the message looked.

Update. Oh, I already saw! Did you write about this window?

Erroneous warning.png

Erroneous warning.png (10.57 KiB) Viewed 3531 times

This is not scary, this is an erroneous message from LibreOffice. If you open the same spreadsheet in OpenOffice, then this warning will not be.

[nophoi]

LibreOffice 6.3.5.2 on Windows 10

[JohnSUN-Pensioner]

(Next time use Alt+PrintScreen instead PrintScreen - it will copy only active window)

[Villeroy]

The latest versions of LibreOffice tightened macro security. Before the change, there were macro warnings when a document had macro code embedded. Now you get the same macro warning in addition when the document may call some installed macro code, for instance pre-installed extensions or macro libraries under "My Macros".

My default template for spreadsheets included empty hooks that once may have been used to call something. This is one of them:

Code: Select all

<script:event-listener script:language="ooo:script" script:event-name="office:start-app"/>

There are many of them. I have no idea how these tags made it into the template. I may have performed some experiments with script events some day.
These hooks are empty. The do not call anything. Nevertheless they trigger the LO macro warning. I used that spreadsheet template for hundreds of spreadsheet uploads to this forum. They all raise a macro warning when opened with latest versions of LibreOffice.

In order to fix this, I would have to download all my spreadsheets of the past 3(?) years, develop a script to remove all the empty <office:event-listerners/> tags (not the actually used ones) and replace the uploads.

Edit: All macro-free spreadsheets derived from any of my templates of the past 12 years trigger the LibreOffice macro warnings. More than 400 documents. In the particular case of Pivot_Month_Person_Category.ods you can simply copy the single sheet into a new document and save that.

[Zizi64]

The latest versions of LibreOffice tightened macro security. Before the change, there were macro warnings when a document had macro code embedded. Now you get the same macro warning in addition when the document may call some installed macro code, for instance pre-installed extensions or macro libraries under "My Macros".

+1

The LO 6.1.6 works for me as I expected, but the newer versions show warnings when the document contains a calling a macro located in MyMacros.

[Villeroy]

This entry actually calls an installed macro on startup that would be called manually via Tools>Macros>Run... "My Macros" --> MRILib -->Module1 -->Mri

Code: Select all

<script:event-listener script:language="ooo:script" script:event-name="office:start-app" xlink:href="vnd.sun.star.script:MRILib.Module1.Mri?language=Basic&location=application" xlink:type="simple"/>

My template from which I created all my spreadsheets of the past 12+ years has empty tags calling nothing at all:

Code: Select all

<script:event-listener script:language="ooo:script" script:event-name="office:start-app" xlink:type="simple"/>

The "xlink:href" attribute is missing which points to the actual macro URL.

I filed a bug, but it is rejected (Resulution="WONTFIX"). https://bugs.documentfoundation.org/sho ... ?id=131624