英文:
Why does this LO-calc formula with end of month count entries that it shouldn't?
问题
这个公式=IF(ISTEXT(AK4),"Data Gap",COUNTIFS(Date,">"&EOMONTH(TODAY(),-1),Examinee,$A4))正在计算从上个月的最后一天开始的条目，但根据我的理解，它应该只计算日期大于上个月底的条目。
有什么想法吗？根据我的知识，这完全不符合逻辑。
提前感谢 =)
英文:
The formula =IF(ISTEXT(AK4),"Data Gap",COUNTIFS(Date,">"&EOMONTH(TODAY(),-1),Examinee,$A4)) is counting entries from the last day of last month, but to my understanding, it should only be counting entries with dates larger than ">" the end of last month.
Any ideas why? It defies all logic according to my knowledge.
Thanks in advance =)
答案1
得分: 1
如@JohnSUN所说，EOMONTH获取指定日期午夜的数字值，为了使其工作，我们应该使用"+1"参数引用月末的后一天，并告诉函数考虑等于或大于该参考点的任何内容。
=IF(ISTEXT(AK4),"数据间隙",COUNTIFS(Date,">="&(EOMONTH(TODAY(),-1)+1),Examinee,$A4))
这不是最直观的公式，您必须记住这个细节。
英文:
So, as @JohnSUN said, EOMONTH takes the numeric value for midnight on the specified day, for it to work, we should reference the day after the end of month with a "+1" argument and tell the function to account for anything equal or bigger to that point of reference.
=IF(ISTEXT(AK4),"Data Gap",COUNTIFS(Date,">="&(EOMONTH(TODAY(),-1)+1),Examinee,$A4))
It is not the most intuitive formula and you have to keep that detail in mind.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论