Assigning a "1" to the current quarter - formula broke today

[BrettOlbrys1]

I have 12 columns of data and each column contains data for a specific quarter (date range). Above each column a number should appear such that the CURRENT QUARTER (4/1/23 - 6/30/23) should have a "1" above it. Each preceding quarter would be 1 less than the previous quarter. For example, Q1 22 should be -4, Q2 22 should be -3, Q3 22 should be -2, Q4 22 should be -1, Q1 23 should be 0, and Q2 23 should be 1.

The formula I am using is this:

=IF(AND(TODAY()>=X226,TODAY()<=Y226),1,1+((12*(YEAR(X226)-YEAR(TODAY()))+MONTH(X226)-FLOOR(MONTH(TODAY()),3)-1)/3))

It was working fine until today, and it is now assigning a "1" to 7/1/23 - 9/30/23, a 0 to the current quarter, etc...

How do I fix this formula so the CURRENT QUARTER will always be assigned the number "1"?

Thanks

Brett

 

[Tetra201]

See if the following formula works for you:

=ROUND((TEXT(Y226,"yyyy mm\/\1\2")-(YEAR(TODAY())+CEILING(MONTH(TODAY())/3,1)/4))*4,0)+1

 

[BrettOlbrys1]

See if the following formula works for you:

=ROUND((TEXT(Y226,"yyyy mm\/\1\2")-(YEAR(TODAY())+CEILING(MONTH(TODAY())/3,1)/4))*4,0)+1

Thanks for the formula. When I apply it, each Quarter is off by -1 (see RESULT). SHOULD BE is what I am looking to get.

RESULT	-4​	-3​	-2​	-1​	0​	1​	2​
SHOULD BE	-3​	-2​	-1​	0​	1​	2​	3​
QTR START DATE	4/1/22​	7/1/22​	10/1/22​	1/1/23​	4/1/23​	7/1/23​	10/1/23​

 

[Tetra201]

Here is an amended formula

=ROUND((TEXT(Y226,"yyyy mm\/\1\2")-(YEAR(TODAY())+CEILING(MONTH(TODAY())/3,1)/4))*4,0)+2

 

[BrettOlbrys1]

=ROUND((TEXT(Y226,"yyyy mm\/\1\2")-(YEAR(TODAY())+CEILING(MONTH(TODAY())/3,1)/4))*4,0)+2

Here is an amended formula

=ROUND((TEXT(Y226,"yyyy mm\/\1\2")-(YEAR(TODAY())+CEILING(MONTH(TODAY())/3,1)/4))*4,0)+2

Worked perfectly! Thanks

 

[Tetra201]

You are welcome.