Dragging down date formulas

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

Sign In or Register to comment.