RE: Data translation by pfdubz
pfdubz
Tue Jul 22 20:32:03 CDT 2008
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
> > > >