Dear Pros,
I am struggling with Countifs trying to combine it with Today()
What I have figured out until now and what is working while testing is the following:
=COUNTIFS(Check[Agent],A29,Check[Date],">=01.07.2020",Check[Date],"<=31.07.2020",Check[Agent],A29,Check[Strike],"Yes") --- works like a charm
=COUNTIFS(Check[Agent],A29,Check[Date],"=23.07.2020",Check[Agent],A29,Check[Strike],"Yes") --- works
As you can see, I am counting in a table called "Check", if an "Agent" has a "Yes" within column "Strike" and check it with a date range in the first example,
while in the second, with a specific date.
Target of the formula should be, to check from "Today" back 28 days (4 weeks), if an Agent got striked with a Yes.
When trying and testing just to use Today(), I am not successful:
=COUNTIFS(Check[Agent],A29,Check[Date],"="&Today(),Check[Agent],A29,Check[Strike],"Yes") --- does not work
=COUNTIFS(Check[Agent],A29,Check[Date],"<="&Today()-28,Check[Agent],A29,Check[Strike],"Yes") --- therefore also does not work
Additionally I found out the following:
If I for example use in Cell I1 Today() and either format the cell for an European date like 23.07.2020 or change it to 23/07/2020 or change it to amercian format 07/23/20 and
refer to it with the following format:
=COUNTIFS(Check[Agent],A29,Check[Date],"="&I1,Check[Agent],A29,Check[Strike],"Yes") --- it does not work
But if I replace the date with 23.07.2020 into Cell I1, then it works.
Therefore my question, what am I doing wrong? Am I using the formula incorrectly, do I have a Format issue, or what is wrong with it?
I am running Office 365
Can you please help?
Thank you
Bookmarks