Looking to create a Klip that only pulls data from the current month - I have attached an of an example of the data set.

What Formula would I need to use to only COUNT cells which have a date in the current month?

• Keely Davison

Hi Tom,

To get all the dates in column C that are in the current month you can use DATE_IN( DATE( @C:C,"M/d/yy"),month).  This gives you a set of true/false values.

You can combine this with other logic to get the count.  For example if you are building a label component you could add a filter to filter on TRUE and then "show aggregated value" will count the results.

Another option is to  use the COUNTIF function.  e.g. COUNTIF(DATE_IN( DATE( @C:C,"M/d/yy"),month))

Here are some links to the function references and filtering docs in our knowledge base.

https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions?query=functions#DATE_IN

https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions?query=functions#COUNTIF

Hope this helps,

Keely

• Tom Sunderland

I've tried to go one step further by building a formula that shows a value ONLY IF column B & column C are both in the current month.

COUNTIF(DATE_IN(DATE(@DOC,B:B;,"M/d/yy"),"month"),AND(DATE_IN(DATE(@DOC,C:C;,"M/d/yy"),"month")))

Doesn't seem to be working? What have I done wrong here?

• Keely Davison

Hi Tom,

You have the right idea. The syntax for the AND needs to be adjusted just a bit.

Try COUNTIF(
AND(
DATE_IN(DATE(@DOC,B:B;,"M/d/yy"),"month"),

DATE_IN(DATE(@DOC,C:C;,"M/d/yy"),"month")))

And for future reference - if you want to get data if either of the columns are in the current month you can use OR instead of AND.

Keely