Data translation
 

I have a datatable containing date in Column A, time in Column B, and value
in Column C. I want to translate (pivot) the table so that I have date in
row 1, time in row 2, and value at the intersect of date and time. How do I
do that???

e.g.
Date Time Value
06/01/2009 8:00 AM 20
06/01/2009 1:00 PM 60
06/01/2009 5:00 PM 65
06/02/2009 8:00 Am 40

Translate to
Date
Time 06/01/2009 06/02/2009
8:00 AM 20 40
1:00 PM 60
5:00 PM 40


Paul

Data translation
 

When you create your pivot table, simply drag the date into column labels and
the time into row labels and it works fine. You will also get totals of the
row values out to the right and totals for the columns at the bottom.

--
Regards,

OssieMac


"pfdubz" wrote:

I have a datatable containing date in Column A, time in Column B, and value
in Column C. I want to translate (pivot) the table so that I have date in
row 1, time in row 2, and value at the intersect of date and time. How do I
do that???

e.g.
Date Time Value
06/01/2009 8:00 AM 20
06/01/2009 1:00 PM 60
06/01/2009 5:00 PM 65
06/02/2009 8:00 Am 40

Translate to
Date
Time 06/01/2009 06/02/2009
8:00 AM 20 40
1:00 PM 60
5:00 PM 40


Paul

Data translation
 

When I do that, My values do not all line up under the date. Instead, I get
a separate column for each date/time value. So in my example, the pivot
table looks like this:

Value Date
Time 6/1/2009 6/1/2009 6/1/2009 6/2/2009
8:00 20 40
14:00 60
20:00 65

--
Paul


"OssieMac" wrote:

When you create your pivot table, simply drag the date into column labels and
the time into row labels and it works fine. You will also get totals of the
row values out to the right and totals for the columns at the bottom.

--
Regards,

OssieMac


"pfdubz" wrote:

I have a datatable containing date in Column A, time in Column B, and value
in Column C. I want to translate (pivot) the table so that I have date in
row 1, time in row 2, and value at the intersect of date and time. How do I
do that???

e.g.
Date Time Value
06/01/2009 8:00 AM 20
06/01/2009 1:00 PM 60
06/01/2009 5:00 PM 65
06/02/2009 8:00 Am 40

Translate to
Date
Time 06/01/2009 06/02/2009
8:00 AM 20 40
1:00 PM 60
5:00 PM 40


Paul

Data translation
 

What version of xl are you using. I have done it in xl2002 and x02007 and it
works perfectly.

Ensure that all of your dates are in fact dates and not a mixture of dates
and text.

Example from my pivot table from xl2007.

Sum of Value Column Labels
Row Labels 6/01/2009 6/02/2009 Grand Total
8:00 AM 20 40 60
1:00 PM 60 60
5:00 PM 65 65
Grand Total 145 40 185

Example from my pivot table from xl2002

Sum of Value Date
Time 6/01/2009 6/02/2009 Grand Total
8:00:00 AM 20 40 60
1:00:00 PM 60 60
5:00:00 PM 65 65
Grand Total 145 40 185

--
Regards,

OssieMac


"pfdubz" wrote:

When I do that, My values do not all line up under the date. Instead, I get
a separate column for each date/time value. So in my example, the pivot
table looks like this:

Value Date
Time 6/1/2009 6/1/2009 6/1/2009 6/2/2009
8:00 20 40
14:00 60
20:00 65

--
Paul


"OssieMac" wrote:

When you create your pivot table, simply drag the date into column labels and
the time into row labels and it works fine. You will also get totals of the
row values out to the right and totals for the columns at the bottom.

--
Regards,

OssieMac


"pfdubz" wrote:

I have a datatable containing date in Column A, time in Column B, and value
in Column C. I want to translate (pivot) the table so that I have date in
row 1, time in row 2, and value at the intersect of date and time. How do I
do that???

e.g.
Date Time Value
06/01/2009 8:00 AM 20
06/01/2009 1:00 PM 60
06/01/2009 5:00 PM 65
06/02/2009 8:00 Am 40

Translate to
Date
Time 06/01/2009 06/02/2009
8:00 AM 20 40
1:00 PM 60
5:00 PM 40


Paul

Data translation
 

My bad,
My date column had dates that were not the same. Same day, but different
time. Once I figured that out and made the dates uniform, it worked out just
fine.
Thanks for your help!
--
Paul


"OssieMac" wrote:

What version of xl are you using. I have done it in xl2002 and x02007 and it
works perfectly.

Ensure that all of your dates are in fact dates and not a mixture of dates
and text.

Example from my pivot table from xl2007.

Sum of Value Column Labels
Row Labels 6/01/2009 6/02/2009 Grand Total
8:00 AM 20 40 60
1:00 PM 60 60
5:00 PM 65 65
Grand Total 145 40 185

Example from my pivot table from xl2002

Sum of Value Date
Time 6/01/2009 6/02/2009 Grand Total
8:00:00 AM 20 40 60
1:00:00 PM 60 60
5:00:00 PM 65 65
Grand Total 145 40 185

--
Regards,

OssieMac


"pfdubz" wrote:

When I do that, My values do not all line up under the date. Instead, I get
a separate column for each date/time value. So in my example, the pivot
table looks like this:

Value Date
Time 6/1/2009 6/1/2009 6/1/2009 6/2/2009
8:00 20 40
14:00 60
20:00 65

--
Paul


"OssieMac" wrote:

When you create your pivot table, simply drag the date into column labels and
the time into row labels and it works fine. You will also get totals of the
row values out to the right and totals for the columns at the bottom.

--
Regards,

OssieMac


"pfdubz" wrote:

I have a datatable containing date in Column A, time in Column B, and value
in Column C. I want to translate (pivot) the table so that I have date in
row 1, time in row 2, and value at the intersect of date and time. How do I
do that???

e.g.
Date Time Value
06/01/2009 8:00 AM 20
06/01/2009 1:00 PM 60
06/01/2009 5:00 PM 65
06/02/2009 8:00 Am 40

Translate to
Date
Time 06/01/2009 06/02/2009
8:00 AM 20 40
1:00 PM 60
5:00 PM 40


Paul