Trying to create a formula that increments the date by 21 days based on the previous row

HLChristiansen

This seems like it should work, but it doesn't.

=DATE([Sprint Start Date]@row - 1) + 21

VBAGuru

From my experience, Auto-numbering is not good and there would be better ways of doing it. I think that is a different discussion as I have other questions regarding that, that are still outstanding. I wish I could answer that part of the question. My best answer would be to delete that column and add in a similar column named "Number". Type in 1 into the first row, then =Number1+1 into row 2 and copy down like before.

Not my personal choice, but how I have been able to get around the Auto-Number issues.

VBAGuru

It would appear that you are trying to use Excel logic to create a column formula. I like your thinking though! You would need to start on the second row of you data and do a formula like:

=DATE([Sprint Start Date]1 + 21)

Then just copy this formula down.

This is presuming that the dates start in row 1.

VBAGuru

If [Sprint Start Date]1 is already a date, then it could be further simplified to just

=[Sprint Start Date]1 + 21

HLChristiansen

that only works for the second row, I need it to keep adding dates down the column, incrementing 21 days each time. I can do that with an individual formula in each row, but at that point, I might as well just write the date in each cell. 😂

VBAGuru

I simply grabbed the cell using the small square in the bottom right corner and dragged as far as I needed. The row # should automatically update as you drag.

VBAGuru

Or you can click ctrl-C on that cell, highlight all of the cells you want to copy it to and paste them to that

HLChristiansen

That works great for the date, it does really weird things to my auto numbering column next to it though. (That is setup to increment

VBAGuru

From my experience, Auto-numbering is not good and there would be better ways of doing it. I think that is a different discussion as I have other questions regarding that, that are still outstanding. I wish I could answer that part of the question. My best answer would be to delete that column and add in a similar column named "Number". Type in 1 into the first row, then =Number1+1 into row 2 and copy down like before.

Not my personal choice, but how I have been able to get around the Auto-Number issues.

VBAGuru

You could still name it as "Sprint #" also.