[Solved] Sum between Dates

Discuss the spreadsheet application

[Solved] Sum between Dates

Postby nophoi » Sun May 10, 2020 3:37 pm

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!
Attachments
testAFG.ods
(11.9 KiB) Downloaded 26 times
Last edited by nophoi on Sun May 10, 2020 4:30 pm, edited 1 time in total.
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: Sum between Dates

Postby JohnSUN-Pensioner » Sun May 10, 2020 4:17 pm

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)

ChooseDateType.png
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sum between Dates

Postby nophoi » Sun May 10, 2020 4:30 pm

Thank you! :) I'm learning to use the Text Import (and Text to Column).
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: [Solved] Sum between Dates

Postby JohnSUN-Pensioner » Sun May 10, 2020 4:33 pm

That's not all.

Rewrite your formula as
Code: Select all   Expand viewCollapse view
=SUMIFS(G1:G6;A1:A6;">=2020-05-01";A1:A6;"< 2020-06-01")

or as
Code: Select all   Expand viewCollapse view
=SUMPRODUCT($G$1:$G$6;YEAR($A$1:$A$6)=2020;MONTH($A$1:$A$6)=5)
Last edited by JohnSUN-Pensioner on Sun May 10, 2020 5:21 pm, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Sum between Dates

Postby nophoi » Sun May 10, 2020 4:42 pm

I tried my formula and it works.

Image
Attachments
testAFG2.ods
(11.6 KiB) Downloaded 23 times
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: [Solved] Sum between Dates

Postby JohnSUN-Pensioner » Sun May 10, 2020 5:25 pm

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?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Sum between Dates

Postby Villeroy » Sun May 10, 2020 5:29 pm

Sum, Min, Max, Average per month for 5 categories and 7 persons in 1000 rows without a single formula: download/file.php?id=38070
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Sum between Dates

Postby nophoi » Mon May 11, 2020 8:40 am

JohnSUN-Pensioner wrote: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 :super:

Villeroy wrote: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!
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: [Solved] Sum between Dates

Postby Zizi64 » Mon May 11, 2020 8:48 am

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   Expand viewCollapse view
=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
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Sum between Dates

Postby JohnSUN-Pensioner » Mon May 11, 2020 9:10 am

nophoi wrote:...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.

nophoi wrote: ...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)

nophoi wrote: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.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Sum between Dates

Postby nophoi » Mon May 11, 2020 9:45 am

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.
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: [Solved] Sum between Dates

Postby JohnSUN-Pensioner » Mon May 11, 2020 9:54 am

nophoi wrote: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. :lol:
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
Erroneous warning.png (10.57 KiB) Viewed 540 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.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Sum between Dates

Postby nophoi » Mon May 11, 2020 10:00 am

Image

LibreOffice 6.3.5.2 on Windows 10 :D
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: [Solved] Sum between Dates

Postby JohnSUN-Pensioner » Mon May 11, 2020 10:04 am

(Next time use Alt+PrintScreen instead PrintScreen - it will copy only active window)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Sum between Dates

Postby Villeroy » Mon May 11, 2020 11:55 am

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   Expand viewCollapse view
<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. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Sum between Dates

Postby Zizi64 » Mon May 11, 2020 1:51 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Sum between Dates

Postby Villeroy » Mon May 11, 2020 2:21 pm

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   Expand viewCollapse view
<script:event-listener script:language="ooo:script" script:event-name="office:start-app" xlink:href="vnd.sun.star.script:MRILib.Module1.Mri?language=Basic&amp;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   Expand viewCollapse view
<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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests