Dragging down date formulas

Popak Roshan

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

Paul Newcome

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) - Variable@row)

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.

Paul Newcome

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) - Variable@row)

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.

Popak Roshan

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

Paul Newcome

Happy to help! 👍️

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