Date Summarization

Electricguy

Hello--

I've run into a puzzle that I cannot solve on my own.

I need to group dates together by year to create a chart to show my leadership team. For example, I need all dates under Open 2021, Pending 2022, so forth. I've played with it for hours and I cannot figure it out. Any help would be most grateful. Thanks!

Matt Johnson

Hi @Electricguy

If the source column is a date type column, you can add a column with the formula =YEAR(source@row).

That will give you the ability to group a report by Year. Or if you are building a dashboard graph you can list the years on a metric sheet and then do COUNTIFS off the source sheet.

I hope that helps.

Matt

Electricguy

Thanks for this assistance.

What if I have the entire date written out? For example, I have the date of the assignment on my sheet as June 10, 2021. Should I reduce the date down to just the year to aggregate the data? Is there a way to separate it out (like Excel) or do I have to manually go down the page and separate the year from the rest of the date?

Paul Newcome

Are you able to provide a screenshot of the source data?

Electricguy

Smartsheet Query - 8.2.2023.png

Please take a look. I'm still trying to figure out this puzzle. I want a formula that will calculate each line by year. It's going on another page, so I'm trying to use Countifs, but it's not quite working out for the year.

Paul Newcome

As long as everything is in date type columns and your data is being stored as dates, you should be able to use:

=COUNTIFS({Date Column}, IFERROR(YEAR(@cell), 0) = 2023)

Electricguy

I think I am going to re-do the Smartsheet as I am still getting the "unparseable" error. Thanks for the help!