Fill Series Date with a twist

[JohnT]

I have a sheet that has column A set as a 'week block' as below
A1 07/06/04 - 13/06/04
A2 14/06/04 - 20/06/04
etc

Is there a way to create a custom date format such as
dd/mm/yy - dd/mm/yy
and then using the fill-series-date, fill down in week blocks

Regards
Joh

 

[Domenic]

Hi John,

I don't think that's possible, but you can work around this b
separating the dates into two columns, using the Fill Series, and the
combining the two columns into one again, if you so wish.

Although, you may want to leave them in two separate columns so tha
you can carry out any calculations based on dates.

Try something like this:

1) Select your column of data
2) Data > Text to Columns
3) Delimited > Next
4) Check Space and Other as your delimiters (typing a hyphen into th
box next to Other)
5) Check "Treat consecutive delimiters as one"
6) Click OK/Finish
7) Use Fill-Series on both new columns

Then, in a new column, enter the following formula to combine the date
and copy down as far as you need to:

=TEXT(A1,"dd/mm/yy")&" - "&TEXT(B1,"dd/mm/yy")

Then, select the new column, Edit > Copy > Edit > Paste Special
Values, and delete the old columns.

Hope this helps