Return 1 if YEAR and MONTH is equal TODAY

Mixer

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.

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.