Return 1 if YEAR and MONTH is equal TODAY

Hi,

I have a formula:

=IFERROR(IF(MONTH([Sale Date]2) = MONTH(TODAY()), 1, IF(MONTH([Sale Date]2) = MONTH(TODAY()) - 1, 2)), " ")

and it worked until we have two years in one sheet. I would like to know how to add YEAR to this formula so that we can compare not only MONTH but also YEAR.

Thanks.

Find more posts tagged with

Sales

Formulas

Services

Comments

Paul Newcome

Try something along these lines...

=IFERROR(IF(AND(MONTH([Sale Date]2) = MONTH(TODAY()), YEAR([Sale Date]2) = YEAR(TODAY())), 1, IF(AND(MONTH([Sale Date]2) = MONTH(TODAY()) - 1, YEAR([Sale Date]2) = YEAR(TODAY())), 2)), " ")

L_123

The OG

=IFERROR(IF(MONTH([Sale Date]2) = MONTH(TODAY()), 1, IF(MONTH([Sale Date]2) = MONTH(TODAY()) - 1, 2)), " ")

The New

=IFERROR(IF(date(year(today()),month(today()),1)=date(Year([Sale Date]2),month([Sale Date]2),1),1,if(Date(Year(today()),month(today()),1)-1 = Date(year(today()),month(today()),1)-1,2

Paul, your will break in january. 1 - 1 = 0 not 12. Learned that the hard way earlier this year.

L_123

tried to edit but got cloudflared. I forgot to add the end of the iferror statement

=IFERROR(IF(date(year(today()),month(today()),1)=date(Year([Sale Date]2),month([Sale Date]2),1),1,if(Date(Year(today()),month(today()),1)-1 = Date(year(today()),month(today()),1)-1,2)),"")

Paul Newcome

Correct. I wasn't paying attention to the details. I just saw the request to include in the YEAR criteria and went with it.

Good catch.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…