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

Agata Wozniak

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

Genevieve P.

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

Andrée Starå

Hi @Agata Wozniak

I hope you're well and safe!

Add something like this.

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!

Agata Wozniak

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

Andrée Starå

@Agata Wozniak

Happy to help!

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

Agata Wozniak

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

Andrée Starå

@Agata Wozniak

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

Agata Wozniak

Yes its a date column

Andrée Starå

@Agata Wozniak

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)

Agata Wozniak

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!

Agata Wozniak

Andrée Starå

@Agata Wozniak

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!

Agata Wozniak

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?

Thank you in advance!

Genevieve P.

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