EXCEL: If DATE is between 10th January to 10th February in the "Date Column", return &quot-CodePudding

So I have an Excel Table with the following columns: Date, Transactions, Amount, and Month Date, Transactions and Amount I copy paste from an original data. I need a formula that automatically determines the Month associated with the "Date" column. Normally, I would do it with:

=TEXT([@Date],"MMM")

However, in this table, the current month is determined from the 11th of this month to the 10th of the next month. Hence, the "Month" column should return "June" if the date is from 11th June to 10th July. If the date is 9th June, the "Month" column should return "May" (even though the month is technically "June"). Consequently, 5th January should return "December", 9th August should return "July", 7th December should return "November", and so on. I tried doing it with "IFS" function, but that becomes extremely long.

=IFS(AND(TEXT(TXN[@Date],"DD")<"10",UPPER(TEXT(TXN[@Date],"MMM"))="JAN"),"DEC",AND(TEXT(TXN[@Date],"DD")>"10",UPPER(TEXT(TXN[@Date],"MMM"))="JAN"),"JAN", AND(TEXT(TXN[@Date],"DD")<"10",UPPER(TEXT(TXN[@Date],"MMM"))="FEB"),"JAN",AND(TEXT(TXN[@Date],"DD")>"10",UPPER(TEXT(TXN[@Date],"MMM"))="FEB"),"FEB", AND(TEXT(TXN[@Date],"DD")<"10",UPPER(TEXT(TXN[@Date],"MMM"))="MAR"),"FEB",AND(TEXT(TXN[@Date],"DD")>"10",UPPER(TEXT(TXN[@Date],"MMM"))="MAR"),"MAR") ... ... ... and so on

That is only for January, February and March and my Excel is already having trouble with it. The result would be absurdly long and I am not even sure that Excel would be able to compute it.

I also want this to be "year-proof", so that I can use the table for multiple years. As a result, I cannot simply use something like "if date is between 11 Jan 2021 and 10 Feb 2021, then it is Jan". I do not want the formula to consider date.

CodePudding user response：

I don't exactly get what you mean by "I do not want the formula to consider date", since that seems necessary. Not sure if the below passes that criteria, but you can use built in date functions I believe.

=IF(DAY([@Date])>10, TEXT([@Date], "MMM"), TEXT([@Date]-28, "MMM"))

For example: