Dragging down date formulas

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Dragging down date formulas

I am trying to drag down dates to populate year days using formulas. It does not go to the next day using drag down. Is there any trick to it? Here is the example


Date1= 01/01/2020

First Cell =$Date$1 - VALUE(YEARDAY($Date$1) - 1) = 01/01/2020

Second Cell =$Date$1 - VALUE(YEARDAY($Date$1) - 2) = 01/02/2020

Third Cell =$Date$1 - VALUE(YEARDAY($Date$1) - 3) = ......


thanks

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    It is because what determines the date is not changed by the dragfill.


    The best way I can think of to enable to use of dragfill is to add in another column that has your variable in it.

    Variable

    -1

    -2

    -3

    etc...


    This can be dragfilled down the column to update the number. Then in your formula to input the date you would replace the number with a cell reference.

    =$Date$1 - (YEARDAY($Date$1) - [email protected])


    Another option to remove the extra column would be to use a COUNT function to count how many rows are above it, then add that as your variable.


    =$Date$1 - (YEARDAY($Date$1) - COUNT($Date$1:Date1))


    If you put that in row 2, it will count 1 and add it to the date. Because we locked in the first row reference, and left the second row reference unlocked, the range will adjust to always count down to the row above the formula.

Answers

  • thank you Paul, I used the 2nd solution and it worked.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️


    I actually thought of the second solution right before I submitted the first. Haha

Sign In or Register to comment.