mickeystanford_alumni
Board Regular
- Joined
- May 11, 2022
- Messages
- 129
- Office Version
- 2021
- Platform
- Windows
- MacOS
Hi guys, I have the below data:
I am using this formula so that I leave the =YEAR(A1) unchanged if it is Oct, Nov, Dec, but I do =Year(A1)-1 if it is different to those 3 months.
I am using this formula: =IFS(TEXT(B1,"MMM")="Jan",YEAR(B1)-1,TEXT(B1,"MMM")="Feb",YEAR(B1)-1,TEXT(B1,"MMM")="Mar",YEAR(B1)-1,TEXT(B1,"MMM")="Apr",YEAR(B1)-1,TEXT(B1,"MMM")="May",YEAR(B1)-1,TEXT(B1,"MMM")="Jun",YEAR(B1)-1,TEXT(B1,"MMM")="Jul",YEAR(B1)-1,TEXT(B1,"MMM")="Aug",YEAR(B1)-1,TEXT(B1,"MMM")="Sep",YEAR(B1)-1,TEXT(B1,"MMM")<>"Oct"&"Nov"&"Dec",YEAR(B1))
Is there any way to instead of using that many ifs, to use just one if with <> to AND/OR?
Thanks a lot.
Jan-22 |
Feb-22 |
Mar-22 |
I am using this formula so that I leave the =YEAR(A1) unchanged if it is Oct, Nov, Dec, but I do =Year(A1)-1 if it is different to those 3 months.
I am using this formula: =IFS(TEXT(B1,"MMM")="Jan",YEAR(B1)-1,TEXT(B1,"MMM")="Feb",YEAR(B1)-1,TEXT(B1,"MMM")="Mar",YEAR(B1)-1,TEXT(B1,"MMM")="Apr",YEAR(B1)-1,TEXT(B1,"MMM")="May",YEAR(B1)-1,TEXT(B1,"MMM")="Jun",YEAR(B1)-1,TEXT(B1,"MMM")="Jul",YEAR(B1)-1,TEXT(B1,"MMM")="Aug",YEAR(B1)-1,TEXT(B1,"MMM")="Sep",YEAR(B1)-1,TEXT(B1,"MMM")<>"Oct"&"Nov"&"Dec",YEAR(B1))
Is there any way to instead of using that many ifs, to use just one if with <> to AND/OR?
Thanks a lot.