Forum Discussion

Manik1920's avatar
Manik1920
Copper Contributor
Mar 24, 2022

If yes then countdown from 30

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.
Formulas and Functions

3 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Mar 24, 2022

    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.

     

     

    • Manik1920's avatar
      Manik1920
      Copper Contributor
      Mar 24, 2022

      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

      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor
        Mar 25, 2022

        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))

         

Resources