date formula - how to add a time frame in the formula (2021 vs 2022)

Options
Agata Wozniak
Agata Wozniak ✭✭✭✭
edited 10/27/22 in Formulas and Functions

Hi

I am trying to add one more piece to my simple formula. I need to count the Released items in report for 2021, and for 2022 separately. So that we can see if the work done last year is smalle or bigger to what was done this year.

So I am trying to add a date condition, preferably one that would be easily modified after Decemeber so that I could show separately 2022 and '2023

=COUNTIFS({Status}, {Released}, {implementation date} ???

is there a way to set a time frame? except for specifying that date is < / = Today


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Agata Wozniak

    You can add more criteria in a SUMIFS function to narrow down the rows that it's adding together.

    For example, if you're looking just for the month of January you can use the MONTH Function like so:

    =SUMIFS({Hours saved}, {domain}, "HROS - HR Core", {Status}, "Released/Done", {implementation date}, IFERROR(YEAR(@cell), 0) = 2022, {implementation date}, IFERROR(MONTH(@cell), 0) = 1)


    For February, just swap out the 1 to be 2:

    =SUMIFS({Hours saved}, {domain}, "HROS - HR Core", {Status}, "Released/Done", {implementation date}, IFERROR(YEAR(@cell), 0) = 2022, {implementation date}, IFERROR(MONTH(@cell), 0) = 2)


    For a month range (e.g. a quarter):

    =SUMIFS({Hours saved}, {domain}, "HROS - HR Core", {Status}, "Released/Done", {implementation date}, IFERROR(YEAR(@cell), 0) = 2022, {implementation date}, IFERROR(MONTH(@cell), 0) <4)

    or

    =SUMIFS({Hours saved}, {domain}, "HROS - HR Core", {Status}, "Released/Done", {implementation date}, IFERROR(YEAR(@cell), 0) = 2022, {implementation date}, IFERROR(MONTH(@cell), 0) >= 4, {implementation date}, IFERROR(MONTH(@cell), 0) <= 6)


    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!