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

Options
✭✭✭✭
edited 10/27/22

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

Options

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

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

IFERROR(YEAR(@cell), 0) = 2022)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Hi,

Thank you for writing so quickly. Unfortunately I have a follow up questions:

* should I add this piece IFERROR after the reference to column where dates are? or should that be instead of this reference?

*in the IFERROR(YEAR(@cell) the reference should be to a column? or to a specific cell?

If i do this way - the value I get is 0. That means its not seeing any of the dates in '22.

=COUNTIFS({Status}, {Released}, {implementation date}, IFERROR(YEAR(@cell), 0) = 2022)

Maybe it has something to do with Date format?

Best!!!

• ✭✭✭✭✭✭
Options

Happy to help!

Is the Implementation Date the range in which you want to check the year?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Yes, the column Implementation date contains all dates for all our items in this format "2022-02-10"

• ✭✭✭✭✭✭
Options

Can you double-check that it's a Date Column?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Yes its a date column

• ✭✭✭✭✭✭
edited 10/27/22
Options

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Hi,

Thank you so much!! it was a simple spelling mistake on my part! it works now just fine. I just had to re-type and re-name all references.

Have a lovely day!

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Excellent!

You're more than welcome!

Remember! 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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Hi, Hope you had a lovely weekend.

Can I have a follow up question? in the formula that you helped with

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

Is it possible to have a more detailed view? ie: SUM of released items by quarter in each year? or by month?

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!