Condensing a long IF statement

[Vonsteiner]

Hello,

I have the following formula in a cell:

=IF(AND([@[Trans. Date]]>=DATE(2018,1,1),[@[Trans. Date]]<=DATE(2018,1,31)),"Jan",IF(AND([@[Trans. Date]]>=DATE(2018,2,1),[@[Trans. Date]]<=DATE(2018,2,28)),"Feb",IF(AND([@[Trans. Date]]>=DATE(2018,3,1),[@[Trans. Date]]<=DATE(2018,3,31)),"Mar",IF(AND([@[Trans. Date]]>=DATE(2018,4,1),[@[Trans. Date]]<=DATE(2018,4,30)),"Apr",IF(AND([@[Trans. Date]]>=DATE(2018,5,1),[@[Trans. Date]]<=DATE(2018,5,31)),"May",IF(AND([@[Trans. Date]]>=DATE(2018,6,1),[@[Trans. Date]]<=DATE(2018,6,30)),"Jun",IF(AND([@[Trans. Date]]>=DATE(2018,7,1),[@[Trans. Date]]<=DATE(2018,7,31)),"Jul",IF(AND([@[Trans. Date]]>=DATE(2018,8,1),[@[Trans. Date]]<=DATE(2018,8,31)),"Aug",IF(AND([@[Trans. Date]]>=DATE(2018,9,1),[@[Trans. Date]]<=DATE(2018,9,30)),"Sep",IF(AND([@[Trans. Date]]>=DATE(2018,10,1),[@[Trans. Date]]<=DATE(2018,10,31)),"Oct",IF(AND([@[Trans. Date]]>=DATE(2018,11,1),[@[Trans. Date]]<=DATE(2018,11,30)),"Nov",IF(AND([@[Trans. Date]]>=DATE(2018,12,1),[@[Trans. Date]]<=DATE(2018,12,31)),"Dec",IF(AND([@[Trans. Date]]>=DATE(2019,1,1),[@[Trans. Date]]<=DATE(2019,1,31)),"Jan",IF(AND([@[Trans. Date]]>=DATE(2019,2,1),[@[Trans. Date]]<=DATE(2019,2,28)),"Feb",IF(AND([@[Trans. Date]]>=DATE(2019,3,1),[@[Trans. Date]]<=DATE(2019,3,31)),"Mar",IF(AND([@[Trans. Date]]>=DATE(2019,4,1),[@[Trans. Date]]<=DATE(2019,4,30)),"Apr",IF(AND([@[Trans. Date]]>=DATE(2019,5,1),[@[Trans. Date]]<=DATE(2019,5,31)),"May",IF(AND([@[Trans. Date]]>=DATE(2019,6,1),[@[Trans. Date]]<=DATE(2019,6,30)),"Jun",IF(AND([@[Trans. Date]]>=DATE(2019,7,1),[@[Trans. Date]]<=DATE(2019,7,31)),"Jul",IF(AND([@[Trans. Date]]>=DATE(2019,8,1),[@[Trans. Date]]<=DATE(2019,8,31)),"Aug",IF(AND([@[Trans. Date]]>=DATE(2019,9,1),[@[Trans. Date]]<=DATE(2019,9,30)),"Sep",IF(AND([@[Trans. Date]]>=DATE(2019,10,1),[@[Trans. Date]]<=DATE(2019,10,31)),"Oct",IF(AND([@[Trans. Date]]>=DATE(2019,11,1),[@[Trans. Date]]<=DATE(2019,11,30)),"Nov",IF(AND([@[Trans. Date]]>=DATE(2019,12,1),[@[Trans. Date]]<=DATE(2019,12,31)),"Dec",IF(AND([@[Trans. Date]]>=DATE(2020,1,1),[@[Trans. Date]]<=DATE(2020,1,31)),"Jan",IF(AND([@[Trans. Date]]>=DATE(2020,2,1),[@[Trans. Date]]<=DATE(2020,2,29)),"Feb",IF(AND([@[Trans. Date]]>=DATE(2020,3,1),[@[Trans. Date]]<=DATE(2020,3,31)),"Mar",IF(AND([@[Trans. Date]]>=DATE(2020,4,1),[@[Trans. Date]]<=DATE(2020,4,30)),"Apr",IF(AND([@[Trans. Date]]>=DATE(2020,5,1),[@[Trans. Date]]<=DATE(2020,5,31)),"May",IF(AND([@[Trans. Date]]>=DATE(2020,6,1),[@[Trans. Date]]<=DATE(2020,6,30)),"Jun",IF(AND([@[Trans. Date]]>=DATE(2020,7,1),[@[Trans. Date]]<=DATE(2020,7,31)),"Jul",IF(AND([@[Trans. Date]]>=DATE(2020,8,1),[@[Trans. Date]]<=DATE(2020,8,31)),"Aug",IF(AND([@[Trans. Date]]>=DATE(2020,9,1),[@[Trans. Date]]<=DATE(2020,9,30)),"Sep",IF(AND([@[Trans. Date]]>=DATE(2020,10,1),[@[Trans. Date]]<=DATE(2020,10,31)),"Oct",IF(AND([@[Trans. Date]]>=DATE(2020,11,1),[@[Trans. Date]]<=DATE(2020,11,30)),"Nov",IF(AND([@[Trans. Date]]>=DATE(2020,12,1),[@[Trans. Date]]<=DATE(2020,12,31)),"Dec",IF(AND([@[Trans. Date]]>=DATE(2021,1,1),[@[Trans. Date]]<=DATE(2021,1,31)),"Jan",IF(AND([@[Trans. Date]]>=DATE(2021,2,1),[@[Trans. Date]]<=DATE(2021,2,28)),"Feb",IF(AND([@[Trans. Date]]>=DATE(2021,3,1),[@[Trans. Date]]<=DATE(2021,3,31)),"Mar",IF(AND([@[Trans. Date]]>=DATE(2021,4,1),[@[Trans. Date]]<=DATE(2021,4,30)),"Apr",IF(AND([@[Trans. Date]]>=DATE(2021,5,1),[@[Trans. Date]]<=DATE(2021,5,31)),"May",IF(AND([@[Trans. Date]]>=DATE(2021,6,1),[@[Trans. Date]]<=DATE(2021,6,30)),"Jun",IF(AND([@[Trans. Date]]>=DATE(2021,7,1),[@[Trans. Date]]<=DATE(2021,7,31)),"Jul",IF(AND([@[Trans. Date]]>=DATE(2021,8,1),[@[Trans. Date]]<=DATE(2021,8,31)),"Aug",IF(AND([@[Trans. Date]]>=DATE(2021,9,1),[@[Trans. Date]]<=DATE(2021,9,30)),"Sep",IF(AND([@[Trans. Date]]>=DATE(2021,10,1),[@[Trans. Date]]<=DATE(2021,10,31)),"Oct",IF(AND([@[Trans. Date]]>=DATE(2021,11,1),[@[Trans. Date]]<=DATE(2021,11,30)),"Nov",IF(AND([@[Trans. Date]]>=DATE(2021,12,1),[@[Trans. Date]]<=DATE(2021,12,31)),"Dec",IF(AND([@[Trans. Date]]>=DATE(2022,1,1),[@[Trans. Date]]<=DATE(2022,1,31)),"Jan",IF(AND([@[Trans. Date]]>=DATE(2022,2,1),[@[Trans. Date]]<=DATE(2022,2,28)),"Feb",IF(AND([@[Trans. Date]]>=DATE(2022,3,1),[@[Trans. Date]]<=DATE(2022,3,31)),"Mar",IF(AND([@[Trans. Date]]>=DATE(2022,4,1),[@[Trans. Date]]<=DATE(2022,4,30)),"Apr",IF(AND([@[Trans. Date]]>=DATE(2022,5,1),[@[Trans. Date]]<=DATE(2022,5,31)),"May",IF(AND([@[Trans. Date]]>=DATE(2022,6,1),[@[Trans. Date]]<=DATE(2022,6,30)),"Jun",IF(AND([@[Trans. Date]]>=DATE(2022,7,1),[@[Trans. Date]]<=DATE(2022,7,31)),"Jul",IF(AND([@[Trans. Date]]>=DATE(2022,8,1),[@[Trans. Date]]<=DATE(2022,8,31)),"Aug",IF(AND([@[Trans. Date]]>=DATE(2022,9,1),[@[Trans. Date]]<=DATE(2022,9,30)),"Sep",IF(AND([@[Trans. Date]]>=DATE(2022,10,1),[@[Trans. Date]]<=DATE(2022,10,31)),"Oct",IF(AND([@[Trans. Date]]>=DATE(2022,11,1),[@[Trans. Date]]<=DATE(2022,11,30)),"Nov",IF(AND([@[Trans. Date]]>=DATE(2022,12,1),[@[Trans. Date]]<=DATE(2022,12,31)),"Dec",""))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

I would like to have it include up to 10 years of checks, but that is way more than the 64 embeds I am allowed. I have played around with it a little, but have not been able to figure out the syntax for a solution to make the formula smaller and include up to 10 years. I would like to have a range of the 10 years it would look at, which could be changed by the user as needed. Any help would be greatly appreciated. Thank you.

 

[Eric W]

Why not just

=TEXT([@[Trans. Date]],"mmm")

 

[Vonsteiner]

Because that would be too easy and would work perfectly. DOH! Sheesh! Sometimes you feel like a nut...

Thanks, I was making it harder than it needed to be.

 

[Eric W]

Sometimes it just takes another set of eyes!

Glad I could help.