Share via

Propagating Dates Down A Column

Anonymous
2011-12-06T22:44:49+00:00

I am looking for a way to propagate dates down a column.  Normally if A1 contains a date, I would put =A1+1 in cell A2 and copy down the column.  In this case I need the formula to preserve a space for 29 February if it is not needed.

For example if Z100 displays 28 February 2008, then Z101 should display 29 February 2008 and Z102 should display 1 March 2008; If Z100 has 28 February 2009 then Z101 should display a blank and Z102 should display 1 March 2009.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2011-12-06T23:41:39+00:00

I was aware of that, but I don't expect to be alive by then... ;-)

Here is a formula that should work for starting dates on or after March 1, 1900:

=IF(A1="",OFFSET(A2,-2,0)+1,IF(AND(DAY(A1)=28,MONTH(A1)=2,MONTH(A1+1)=3),"",A1+1))

(As you may know, Excel contains a deliberate error - it counts 1900 as a leap year)

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-12-06T23:56:25+00:00

    Thanks!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-12-06T23:18:01+00:00

    Thank you.

    The formula works in most cases.  For year 2100, however, it does not leave the blank in place.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2011-12-06T23:05:07+00:00

    With a date in A1, enter the following formula in A2:

    =IF(A1="",OFFSET(A2,-2,0)+1,IF(AND(MOD(YEAR(A1),4)>0,MONTH(A1)=2,DAY(A1)=28),"",A1+1))

    and fill down. The OFFSET function is used because we can't refer to cell A0. This wouldn't be needed if we started further down.

    Was this answer helpful?

    0 comments