Counting Question

[BrettOlbrys1]

I am sure this is easy, but this is what I want to happen. I have a table with multiple columns based on a start date and end date. I want to assign "1" to the current quarter and each quarter thereafter, should respectively be assigned "2", "3", "4", etc...

So because today is 1/6/23, a "1" is assigned to the date range of 1/1/23 to 3/31/23 and the following quarters are 2, 3, etc. When there is a date next quarter, for example 5/2/23, a "1" will be assigned to the range (4/1/23 - 6/30/23).

What formula would I use for this?

Assigned Value	"0"​	"1"​	"2"​	"3"​
Start Date	10/1/22​	1/1/23​	4/1/23​	7/1/23​
End Date	12/31/22​	3/31/23​	6/30/23​	9/30/23​

 

[kvsrinivasamurthy]

For 5/2/23 What are the values of B1, C1, D1, E1

 

[BrettOlbrys1]

For 5/2/23 What are the values of B1, C1, D1, E1

-1, 0, 1, 2

So each corresponding quarter changes in value by "1" when you are in a new quarter. But the CURRENT QUARTER always gets assigned "1" by the formula.

 

[kvsrinivasamurthy]

In B1 then copy across.

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

 

[BrettOlbrys1]

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

Worked perfectly, thank you!