complete blank date fields based on last date in cell above

I have a spreadsheet full of records, in column A is a date, however, not every row of data is full of date entries, when it should. What I need to do is pre-populate the dates, basically if the cell has no date value, it should be populated with the date immediately above (or not in the cell above, the one aboive that), this rule only counts until the next cell in the column with a date in, and then the same process applies.

 i.e some sample data below.I would ideally like to create a "date 2" column in column B so I can view the 2 side by side to ensure it has worked properly.

A1 - 12/08/2015
A2 - (empty)
A3 - (empty)
A4 - 21/08/2015
A5 - (empty)
A6 (27/08/2015
A7 (empty)

In the example above, A2 and A3 would need to be populated with 12/08/2015 (as the date in A1), A5 would need to be populated with 21/08/2015 (as thats the last date shown in A4), A7 would need to be populated with 27/08/2015 (as thats the last date shown in A7). How can this be achieved in excel, can you provide any pointers?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try

In B1 =A1
in B2 =IF(A2="",B1,A2) and copy down


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Okay follow these steps...

1. Select you range which you want to fill..In this case its A1 to A7 which you want to fill so select that..
2. Now Press F5 which will open the dialogue box of Goto
3. In that dialogue box select the option of special.
4. That will open the dialogue box of goto special.. In that select that option of Blanks.
5. Now as soon you select blanks and hit okay you will see it will select all the blank cells in your region with focus on cell a2.
6. Now on cell a2 which has focus apply formula assuming you have all the blank cells selected as well =a1 and rather then doing normal ctrl first and then do enter..
7. Volla now you will see a formula in all the blank cells link to cell above which is what you are looking for..
8. Now you can simply select your region and convert these formulas to paste special-->values and which will convert this formulas to values...

Alternatively you can do this using another column by using this formula..

so in B1 the formula will be=a1

Now in B2 the formula will be =if(a2="",b1,a2) and simply drag this in B Column which will do what you are looking for..

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.