Dates in a column but with variable blanks

Hi All,

We are going on a caravan trip round Australia - about 17,000km over 3 months.

I usually create an excel sheet detailing where we expect to go and distances between the towns to be visited. If I do a sheet with auto fill the date in column A that's easy but we might pass through a number of towns before we stop for the night, or a number of nights - so auto fill cannot handle the variable number of rows that require no date.

I need a formula that will take into account the variable "blank" rows for the nights we do NOT stay. Also allow changes to the days where we stay by recopying the formula down from that amendment.

A sample worksheet is attached.

Any suggestions would be gratefully received.


Regards

DWWA

Thanks for your speedy reply. Unfortunately your solution does not do what I intend.

1. To create a start point, I put a start date into A2...ie 5th March 2021
2. Then in A3 have a formula that looks at B3, B4,etc that do NOT have a 1 indicating we are still traveling and hence the date is to blank. This formula is copied down to the end of the trip.
3. When it finds a 1 in the overnight column then it needs a date in A5 - being one day more than the previous date ie 5th March in A2. Because there is a 1 in B6 and B7 then the date will appear.


I hope this clarifies what I am trying to archive.


Thanks DWWA

Brilliant!!! Works exactly the way I wanted it to.

Sorry to that you are in lock down. One advantage of being one of the most isolated cities in the world and being an island in an island there only half a dozen roads in and out so no one allowed in or out! Result has been that life has been fairly "normal" for the last year. However no one is going to Bali (or any where else in the world) so every caravan park in the state is full!!!

Thanks again.....now I will go away and figure out how it does it!

davidwwa