I have the following data set:

I would like to dynamically calculate "Quarter to date" in a different cell.

Basically if we are in the first month of the quarter, like January, then look at January and divide by the number of days of January multiplied by the days that have passed. If we are in Feb 21, then it would be Jan 21, plus the objective for Feb 21 divided by the days of feb and multiplied by the days that have passed.

Now, once we go to the second quarter, which starts in April, I would like to have the same but starting from April since that is the first month of the quarter, then look at May, then June, etcetera.

Is there a way to construct something like this?

CodePudding user response：

You could do:

=IF( ISEVEN( SUM( INT((MONTH(A1:B1)-1)/3) ) ), A3   B2, B2 )

but for this to work, you need to put = A2 in cell A3.

CodePudding user response：

If I well understand, assuming 1st days of each month are in B1:M1, and values in B2:until now, try

=arrayformula(SUMPRODUCT(B2:M2,(INT((month(B1:M1)-1)/3) 1)=(INT((month(today())-1)/3) 1))/(datedif(date(year(today()),(INT((month(today())-1)/3))*3 1,1),today(),"d") 1)*SUMPRODUCT(DATEDIF(B1:M1,EOMONTH(B1:M1,0),"d") 1,(INT((month(B1:M1)-1)/3) 1)=(INT((month(today())-1)/3) 1)))