Year-to-year best practices when it comes to metrics based on dates?

Greetings-

I need some advice in regards to best practices when it comes to tracking multiple years and what to do when one year ends. For example, my SS below tracks submissions via the form by month and year using the formula below for January.

=COUNTIFS({Community Engagement Request Range 1}, "1", {Community Engagement Request Range 2}, "2023")

When 2023 ended, I had to copy and paste every month into 2024 columns then manually update each line of code to say 2024 where it used to say 2023. There has to be some way or best practice to accomplish these metrics per year but not have to update lines of code for every SS I have created at the end of each year to prepare for the next calendar year change?

What am I missing to make this easier? Thanks for any info!

Find more posts tagged with

Formulas

Best Practice

Time Calculation

Accepted answers

All comments

Andrée Starå

Hi @SSParks

I hope you're well and safe!

I recommend adding a Sheet Summary Field with the Year and referencing it in the formula.
What I usually do in my client solutions, which have Year, Months, Days, or similar, is to add so-called helper rows somewhere in the sheet (usually at the top) with the Year in each cell, Month#, and so on, and I would still put the Year in the Sheet Summary and reference it in the Year row at the top)
In your structure, you could do it similarly, but in so-called helper columns instead.

Make sense?

Would any of those options work/help?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SSParks

Hello! I follow you a little bit. I picture what you're saying as somewhere on the form I have a cell that asks the current year (which Id have 2024 in) and my formulas would reference that cell. What happens when the year ends and you update the cell to the new year but still need a record of the previous years info for a multi year comparison? Do you have any formula examples I could see and play with in regards to referencing a year summary cell bc Im not sure yet how that would work.

Also, what I have been doing to count the number of items in months and years has been I have a folumla that breaks down the date into a month number and a year number seen below then I do a count formula of how many 6's and 21 appear to count how many items in June of 2021. Is this a normal approach to record events for each month in a year? Thanks for any info!

SSParks

https://community.smartsheet.com/discussion/comment/416140#Comment_416140

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.