-
AgilityAssoc.CanadaAsked on January 25, 2020 at 4:19 PM
Hi,
My form 81646514559970 is integrated with g-sheets. The data is separated out to other sheets. On one of these sheets if I use slightly different calculations I sometimes get different results.
First calc is done like this...
=SUMIFS($F$3:$F,G$3:G,">="&$K4,G$3:G,"<="&eomonth($K4,0))
The second method is done this way. Highlighted is the change.
=SUMIFS($F$2:$F,$G$2:$G,">="&$K4,$G$2:$G,"<="&EDATE(($K4),1))
As I said some values return the same and others differently.
Why is "eomonth" and "EDATE" not always giving the same value. Highlighted values in image illustrate this.
Thanks
RobertLink to demo sheet, with real data, is enabled for editing.
-
Girish JotForm SupportReplied on January 25, 2020 at 5:55 PM
Please note that there is a slight difference between the purpose of EDATE and EOMONTH.
The purpose of the EDATE function is to shift date n months in future or past and that of the EOMONTH function is to get the last day of month n months in future or past.
So based on your requirement, you can use either one in the excel sheet.
-
AgilityAssoc.CanadaReplied on January 26, 2020 at 10:00 AM
OK,
EOMONTH A1,0), value, on last day of the month, 0 months after start date (A1)
EDATE(A1,1), value, 1 month after start date (A1).
Do I have these two options correctly understood?
If I can use either, why are some of the calculated values different then, in some cases by a significant amount.? This tells me that I should use one over the other, but which one?
Thank you,
Robert -
VincentJayReplied on January 26, 2020 at 10:32 AM
I am not sure if this is still a JotForm related issue but I will try to answer it with a little spreadsheet knowledge.
I checked the formula, it is not the same as the first one:
Is that a correct formula or not? If not, please try to fix it so the answer is the same as the other.
Let us know if I misunderstood the issue.
-
AgilityAssoc.CanadaReplied on January 26, 2020 at 8:08 PM
Yes it is correct, if EOMONTH has a one or EDATE a zero then the answers are all out of wack.
OK, I will find the answer on a support forum then.
Thanks for trying.
Best,
Robert