Returning a value if a date is between 3 different ranges

[jameslarsen1995]

Let's say I have a date in A1 and I have 3 different date ranges. My date ranges are:
1/1/2020 - 12/31/2020, 1/1/2021 - 12/31/2021, and 1/1/2022 - 12/31/2022

So if my date in A1 is between the first set, then I want it to return a '1', if it falls between the second set then return '2', etc.

Thank You

 

[steve the fish]

Try:

=YEAR(A1)-2019

 

[jameslarsen1995]

Thanks so much for this. What if I need to change my date ranges to 1/31/20-1/31/21 and 2/1/21-1/31/2022 and 2/1/22-1/31/2022?

 

[steve the fish]

I would create a lookup table if the goalposts were moving. Depends as i cant see you sheet. However if you cant you could do something like:

=IF(A1>=DATE(2023,1,31),"Out of range",IF(A1>=DATE(2022,1,31),3,IF(A1>=DATE(2021,1,31),2,IF(A1>=DATE(2020,1,31),1,"Out of range"))))