Count by Quarter, in Current Year, if "published" status

Jessica Griffin

I am attempting to create a metric that counts a line item based on a published date, but I want the count to be by current year quarter.

Using "Date Published"

Completed Policies (current year) Tracked by Published 'Date'

Q1 #UNPARSEABLE

Q2

Q3

Q4

Paul Newcome

Sure thing.

=COUNTIFS({Source Sheet Date Column}, AND(@cell >= DATE(YEAR(TODAY()), 01, 01), @cell < DATE(YEAR(TODAY()), 04, 01)))

Paul Newcome

Do you have a column that tracks the quarter already, or do you need to establish the quarter based on the dates within the COUNTIFS?

Jessica Griffin

I am not tracking the quarter. I would like to establish the quarter based on the dates within @Paul Newcome

Paul Newcome

Ok. In that case you will want to use something similar to the below but will need to change the dates accordingly for each of the different quarters.

=COUNTIFS({Source Sheet Date Column}, AND(@cell >= DATE(2021, 01, 01), @cell < DATE(2021, 04, 01)))

Jessica Griffin

@Paul Newcome Thank you. This formula does work. Is there a way to set it for current year instead of having 2021 or 2022 written into the formula so that the sheet leads itself to longevity and won't have to be rewritten in January?

Paul Newcome

Sure thing.

=COUNTIFS({Source Sheet Date Column}, AND(@cell >= DATE(YEAR(TODAY()), 01, 01), @cell < DATE(YEAR(TODAY()), 04, 01)))

Jessica Griffin

Thank you @Paul Newcome !!

Paul Newcome

Happy to help. 👍️

hannahstayo

Hi @Paul Newcome I am looking to do something similar, but I have created a helper column that already tracks the quarter. I'd like to have a column that counts anything in the current quarter, and also anything in the previous quarter.