Count by Quarter, in Current Year, if "published" status
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
Best Answer
-
Sure thing.
=COUNTIFS({Source Sheet Date Column}, AND(@cell >= DATE(YEAR(TODAY()), 01, 01), @cell < DATE(YEAR(TODAY()), 04, 01)))
Answers
-
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?
-
I am not tracking the quarter. I would like to establish the quarter based on the dates within @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)))
-
@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?
-
Sure thing.
=COUNTIFS({Source Sheet Date Column}, AND(@cell >= DATE(YEAR(TODAY()), 01, 01), @cell < DATE(YEAR(TODAY()), 04, 01)))
-
Thank you @Paul Newcome !!
-
Happy to help. 👍️
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!