generating dates

[Guest]

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

 

[joeu2004]

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.

 

[Guest]

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

 

[Bob Phillips]

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)

 

[joeu2004]

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.

 

[joeu2004]

PS....

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?

 

[joeu2004]

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. ;-)

 

[Guest]

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

 

[Guest]

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

Copy as far as needed

 

[joeu2004]

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).