Automated formula for dates

Josephine

Hi All,

We have internal learning modules, and once completed you are issued a 'badge'. I want to create a report that will tell me each month how many badges were issued of each badge for that month and then the total for the month.

i.e. Dec 2021 10 IT badges, 5 HSE, 6 Ops badges = 21 for December

I have created a new smartsheet that has a column for each badge, rows for each month and links back to the sheet where badges are recorded per employee. I have the formula -

=COUNTIFS({Badges Issued Range 1}, >=DATE(2021, 12, 1), {Badges Issued Range 1}, <=DATE(2021, 12, 31))

This formula is working fine but it means each month I have to change the dates for the 14 badges. any suggestions on how to make it more automatic?

David Tutwiler

Yes, same thing here, but slightly different syntax. The idea is that the dates 12-1 through 12-31 is the same thing as MONTH = 12. So if you converted your formulas to:

=COUNTIF({Badges Issued Range1}, IFERROR(MONTH(@cell), 0) = Month@row)

David Tutwiler

Instead of using DATE, have you looked into using MONTH? If you do that, and add 1 more column that numbers your rows 1-12, then point the MONTH formula to the month number on the row, you would not have to add anything for new months.

Something like =COUNTIF({Badges Issued Range 1}, MONTH(Month@row))

If you foresee this being used for multiple years, you could also add a year column and do the same thing with the YEAR function.

=COUNTIFS({Badges Issued Range 1}, MONTH(Month@row), {Badges Issued Range 1}, YEAR(Year@row))

Josephine

Hi @David Tutwiler

Thanks for taking time to respond - I hadn't come across the month function so will keep that one for the future!

Doesn't get the outcome I am after with this scenario.

First sheet shows per employee what badge has been achieved and the date achieved. The Employee name column in the first sheet has a different name per line.

I started another sheet to try and collate the data and my formula

=COUNTIFS({Badges Issued Range 1}, >=DATE(2021, 12, 1), {Badges Issued Range 1}, <=DATE(2021, 12, 31))

refers to the first sheet, each column refers to a different column in the first sheet so there are 14 different formulas for each month as there are 14 badges.

I am trying to be able to calculate each month (and show data from each month for trends) without having to change the dates in 14 columns of formulas - any suggestions?

David Tutwiler

Yes, same thing here, but slightly different syntax. The idea is that the dates 12-1 through 12-31 is the same thing as MONTH = 12. So if you converted your formulas to:

=COUNTIF({Badges Issued Range1}, IFERROR(MONTH(@cell), 0) = Month@row)

Josephine

@David Tutwiler Thank you so much for clarifying that. Had another go with adding in the year and it works perfectly 😁

Final formula incase anyone else sees it -

=COUNTIF({Badges Issued Range 1}, AND(IFERROR(MONTH(@cell), 0) = Month@row, IFERROR(YEAR(@cell), 0) = Year@row))

David Tutwiler

That's awesome to hear! Glad you got it working.