Rearranging data in one column into two columns
 

I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below:

Present display Would like displayed as this
5/31/07 5/31/07 11:30
11:30 5/31/07 13:20
5/31/07 6/1/07 10:30
13:20 6/1/07 15:30
6/1/07
10:30
6/1/07
15:30

I apreciate the assistance.
--
Constance

Rearranging data in one column into two columns
 

one method
if 5/31/07 is in A2
in b2 enter =A3
copy B2 and B3 and paste form B4 to the end of your data
Use autofilter on column B and select non-blanks.


"Constance" wrote:

I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below:

Present display Would like displayed as this
5/31/07 5/31/07 11:30
11:30 5/31/07 13:20
5/31/07 6/1/07 10:30
13:20 6/1/07 15:30
6/1/07
10:30
6/1/07
15:30

I apreciate the assistance.
--
Constance

Rearranging data in one column into two columns
 

This formula worked for me:
=OFFSET($A$1,(ROW()*2)-2,0,1,1)+OFFSET($A$1,(ROW()*2)-1,0,1,1)

For this formula to work, it must start on the same row that your
"present data" range starts. Where the formula says "$A$1", enter the
address of the cell that starts the present data range. Also, the
formula assumes recognizes the entries as dates and times (not text,
for instance); format the formula cells as date/time.

Dave O

Rearranging data in one column into two columns
 

That works! Thanks much.
--
Constance


"bj" wrote:

one method
if 5/31/07 is in A2
in b2 enter =A3
copy B2 and B3 and paste form B4 to the end of your data
Use autofilter on column B and select non-blanks.


"Constance" wrote:

I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below:

Present display Would like displayed as this
5/31/07 5/31/07 11:30
11:30 5/31/07 13:20
5/31/07 6/1/07 10:30
13:20 6/1/07 15:30
6/1/07
10:30
6/1/07
15:30

I apreciate the assistance.
--
Constance

Rearranging data in one column into two columns
 

Say your original list is in Column A.

Enter this formula in Say C1:

=INDEX($A:$A,2*ROWS($1:1)+COLUMNS($A:A)-2)

THEN, copy across to D1.

NOW, format C1 to Dates, and D1 to Time.

Select *both* C1 and D1, and drag down the 2 cell selection to copy as
needed.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Constance" wrote in message
...
I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below:

Present display Would like displayed as this
5/31/07 5/31/07 11:30
11:30 5/31/07 13:20
5/31/07 6/1/07 10:30
13:20 6/1/07 15:30
6/1/07
10:30
6/1/07
15:30

I apreciate the assistance.
--
Constance

Rearranging data in one column into two columns
 

ARGH! My apologies- hope I catch you before you go off on a wild goose
chase. Use this formula instead:
=OFFSET($A$1,ROW()-1,0,1,1)+OFFSET($A$1,ROW(),0,1,1)

For this formula to work, it must start on the same row that your
"present data" range starts. Where the formula says "$A$1", change the
$A to the column that holds your present data range (include the $
anchor). Also, the formula assumes recognizes the entries as dates and
times (not text, for instance); format the formula cells as date/
time.

Dave O

Rearranging data in one column into two columns
 

Quick way would be to Select the column starting with the first time,
and copy and then paste in the second column next to the first date.
Then select both columns, Data Sort, and then delete the rows that start
with "time".

Constance wrote:
I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below:

Present display Would like displayed as this
5/31/07 5/31/07 11:30
11:30 5/31/07 13:20
5/31/07 6/1/07 10:30
13:20 6/1/07 15:30
6/1/07
10:30
6/1/07
15:30

I apreciate the assistance.