If yes then countdown from 30

[Manik1920]

Hi guys/girls,

Trying to figure out a formula for the following:

If column a1 is "yes" then countdown in days in column b1 starts at 30 and counts down to -120
If no then countdown is same as C1 + 1 day.

Thanks.

[#2]

Hi @Manik1920 

here is my proposal.

In cell B1 put:

=IF(A1="yes";30;C1+1)

In cell B2 put:

=IFERROR(IF(B1-1>-120;B1-1;"");"")

Then copy the formula from B2 down to as many rows as you need.

If you use Microsoft 365, you can achieve this with just one formula.

In B1 enter:

=IF(A1="yes";SEQUENCE(151;1;30;-1);SEQUENCE(C1+122;1;C1+1;-1))

Depending on your local settings, you might need to replace the ; with , in the formulas.

[#3]

Hi @Martin_Weiss  thank you for this

I wanted the countdown to be done daily. Ie if today is 30 tomorrow is 29, drop 1 per day.

Not sure if this is possible

[#4]

Hi @Manik1920 

so, B1 should start with the current day? Then you could use:

=IF(A1="yes";DAY(TODAY());C1+1)

And in A2 the same formula as in my first answer:

=IFERROR(IF(B1-1>-120;B1-1;"");"")

And if you have M365, you can use:

=IF(A1="yes";SEQUENCE(121+DAY(TODAY());1;DAY(TODAY());-1);SEQUENCE(C1+122;1;C1+1;-1))