Search code examples
datecountiflibreoffice-calc

Why does this LO-calc formula with end of month count entries that it shouldn't?

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 =)

Solution

  • 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.