In Google sheets, I need to add X number of days to a passed date repeatedly until before it surpasses today's date.
In my example sheet, assume today's date is always 7/29/2023. If I am incrementing 7/18/2023 by 3 days repeatedly before reaching today's date of 7/29/2023, then the date would stop at 7/27/2023. If I add 3 more to 7/27/2023, then that would surpass today's date and be 7/30/2023 which shouldn't happen. The output needs to stop at the final incriment before surpassing or equaling today's date.
Please write your formula in the form of an array formula that calculates down the column.
map(B4:B,C4:C,lambda(b,c,if(or(c="",b=""),,let(Σ,sequence(days(B1,c)+1,1,c,b),xlookup(B1-1,Σ,Σ,,-1)))))