hi all,

XP SP2, Office 2003 Pro SP2,

i want to generate a column of a pair of consecutive dates (format:
dd/mm/yyyy) like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007.

with one dates and consecutive - this can be easily achieved by dragging the
box handle down but with pairing like the way i wanted, this does not work.
would appreciate advice and help on how this may be achieved.

thank you,
jes

xppuser wrote:
i want to generate a column of a pair of consecutive dates (format:
dd/mm/yyyy) like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007.

with one dates and consecutive - this can be easily achieved by dragging the
box handle down but with pairing like the way i wanted, this does not work.
would appreciate advice and help on how this may be achieved.

Enter the first two pairs, e.g: 9/10/2006 9/10/2006 in one row, and
9/11/2006 9/11/2006 in the next row. Highlight the range encompassing
all 4 cells, then drag the box handle down.

hi joeu,

your suggestion does not work on my machine. what your suggestion did was to
copy the four cells i.e. 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006
(highlighted - dragged) becomes 09/10/2006, 09/10/2006, 10/10/2006,
10/10/2006, 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006, 09/10/2006,
09/10/2006, 10/10/2006, 10/10/2006,... and so on.

regards,
jes


" wrote:

xppuser wrote:
i want to generate a column of a pair of consecutive dates (format:
dd/mm/yyyy) like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007.

with one dates and consecutive - this can be easily achieved by dragging the
box handle down but with pairing like the way i wanted, this does not work.
would appreciate advice and help on how this may be achieved.

Enter the first two pairs, e.g: 9/10/2006 9/10/2006 in one row, and
9/11/2006 9/11/2006 in the next row. Highlight the range encompassing
all 4 cells, then drag the box handle down.

Put the Sep date in A1 and A2.

In A3 add =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
A4 add: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

drag-copy A3:A4 down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"xppuser" wrote in message
...
hi joeu,

your suggestion does not work on my machine. what your suggestion did was
to
copy the four cells i.e. 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006
(highlighted - dragged) becomes 09/10/2006, 09/10/2006, 10/10/2006,
10/10/2006, 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006, 09/10/2006,
09/10/2006, 10/10/2006, 10/10/2006,... and so on.

regards,
jes


" wrote:

xppuser wrote:
i want to generate a column of a pair of consecutive dates (format:
dd/mm/yyyy) like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007.

with one dates and consecutive - this can be easily achieved by
dragging the
box handle down but with pairing like the way i wanted, this does not
work.
would appreciate advice and help on how this may be achieved.

Enter the first two pairs, e.g: 9/10/2006 9/10/2006 in one row, and
9/11/2006 9/11/2006 in the next row. Highlight the range encompassing
all 4 cells, then drag the box handle down.

xppuser wrote:
your suggestion does not work on my machine. what your suggestion did was to
copy the four cells i.e. 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006
(highlighted - dragged) becomes 09/10/2006, 09/10/2006, 10/10/2006,
10/10/2006, 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006, 09/10/2006,
09/10/2006, 10/10/2006, 10/10/2006,... and so on.

I probably misunderstood the format of your data. Perhaps you could
clarify by specifying cell names.

Your original posting described "a column of a pair of consecutive
dates [...] like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007". Since I saw
neither a column of data nor consecutive dates, I struggled to make
some sense of it by reading between the lines.

Since you said "with one dates and consecutive - this can be easily
achieved by dragging the box handle down", I assumed you meant, for
example, that you put 9/10/2006 in a cell, then drag the box handle
down to create a column of consecutive dates with 9/10/2006, 9/11/2006,
9/12/2006, etc. At least, that is what happens when I try it.

Therefore, I ass-u-me-d the "column of a pair of consecutive dates"
really is a pair of columns of consecutive dates like the single column
created above. For example, if A1 and B1 both 9/10/2006, I thought you
want A2 and B2 to contain 9/11/2006, A3 and B3 to contain 9/12/2006,
etc. The method I described accomplishes that. (I am using the same
version of Excel that you are.)

However, based on your latest comment, I must conclude that my
assumptions are wrong. But in that case, honestly I have no idea what
you are talking about.

PS....

I wrote:
Since you said "with one dates and consecutive - this can be easily
achieved by dragging the box handle down", I assumed you meant, for
example, that you put 9/10/2006 in a cell, then drag the box handle
down to create a column of consecutive dates with 9/10/2006, 9/11/2006,
9/12/2006, etc. At least, that is what happens when I try it.

And if you mean, instead, that you put 9/10/2006 in A1 and 10/10/2006
in A2, for example, then dragged the pair of cells down to create the
"consecutive" dates 9/10/2006, 10/10/2006, 11/10/2006, etc, and if your
intention now is to put 9/10/2006 into A1 and B1, 10/10/2006 into A2
and B2, 11/10/2006 into A3 and B3, etc, then my original suggestion
seems work just fine. To clarify....

Put 9/10/2006 into A1 and B1, and put 10/10/2006 into A2 and B2. Then
highlight the 4-cell range A1:B2 and drag them down. I get 11/10/2006
in A3 and B3, 12/10/2006 in A4 and B4, etc. Is that what you want?

Bob Phillips wrote:
Put the Sep date in A1 and A2.
In A3 add =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
A4 add: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
drag-copy A3:A4 down

Works for the example given and most other dates. But beware of dates
that include the 29th, 30th and 31st of the month. ;-)

hi Bob, joeu,

Bob your suggestion worked! i modified it a bit taking into account my date
format (i.e. dd/mm/yyyy). it seems to automatically recognized 30th and 31st
months.

joeu - i should have clarified it a bit more. what i wanted was A1
09/10/2006, A2 09/10/2006, A3 10/10/2006, A4 10/10/2006,..., A((n-1)th)
11/10/2006, A(nth) 11/10/2006. sorry if it causes confusion.

thank you for your suggestions.

regards,
jes

A1 =09/10/2006
A2 =IF(COUNTIF($A$1:A1,A1)=1,A1,A1+1)

Copy as far as needed


"xppuser" wrote:

hi all,

XP SP2, Office 2003 Pro SP2,

i want to generate a column of a pair of consecutive dates (format:
dd/mm/yyyy) like so: 09/10/2006, 09/10/2006, 10/10/2006, 10/10/2006,
11/10/2006, 11/10/2006,..., 08/10/2007, 08/10/2007.

with one dates and consecutive - this can be easily achieved by dragging the
box handle down but with pairing like the way i wanted, this does not work.
would appreciate advice and help on how this may be achieved.

thank you,
jes

xppuser wrote:
joeu - i should have clarified it a bit more. what i wanted was A1
09/10/2006, A2 09/10/2006, A3 10/10/2006, A4 10/10/2006

Sorry, my bad.

Bob your suggestion worked! i modified it a bit taking into account my date
format (i.e. dd/mm/yyyy). it seems to automatically recognized 30th and 31st
months.

Not sure what you mean by the last sentence. But beware.... For
1/31/2006, date(year(A1),month(A1)+1,day(A1)) results in 3/3/2006.
Similarly for 1/29/2006 and 1/30/2006. For 3/31/2006, date(...)
results in 5/1/2006. If that is what you want, fine. But many people
wish that 1/31/2006 "plus one month" would result in 2/28/2006 (or 2/29
in leap years).