pivot table -repeating "months or days" after grouping

[Lucille]

I have data that includes the month, day, hour, min. When I run th
pivot table function to reduce the data to hourly I need the date t
repeat in the date column.

Example:

9/19/2002 0:00 2.7
9/19/2002 0:10 3.7
9/19/2002 0:20 3.8
9/19/2002 0:30 4.6
9/19/2002 0:40 4.8
9/19/2002 0:50 4.8
9/19/2002 1:00 5.6
9/19/2002 1:10 5.2

What I get when I group the Pivot table:

Sep 19-Sep 12 AM 2.7
- - 1 AM 3.7
- - 2 AM 3.8
- - 3 AM 4.6
- - 4 AM 4.8

What I want is:

Sep 19-Sep 12 AM 2.7
- 19-Sep 1 AM 3.7
- 19-Sep 2 AM 3.8
- 19-Sep 3 AM 4.6
- 19-Sep 4 AM 4.8

-- or --


Sep 19-Sep 12 AM 2.7
Sep 19-Sep 1 AM 3.7
Sep 19-Sep 2 AM 3.8
Sep 19-Sep 3 AM 4.6
Sep 19-Sep 4 AM 4.8

Any Ideas??

 

[Dave Peterson]

I don't think you can do it and keep it a pivottable.

When I have to have things like this, I'll edit|copy, edit|paste special|values.

Then I can manipulate the data the way I want. (In fact, I've copied the sheet
and manipulated that--to preserve the pivottable.)

If you can do this, Debra Dalgleish has some notes on how to fill those cells
at:
http://www.contextures.com/xlDataEntry02.html

 

[Lucille]

Thanks,


I have10 sites with 10 years of data each to go through... I've don
the cut and paste for a year's worth of hourly data don't want t
do that again.

I wrote a formula to copy down the column that seems to fill in th
date and time, as long as no records are skipped, after I've complete
the pivot table . The key here was to use military time instead of th
am/pm that the cells show. (That took more than 15 minutes to figur
out).

Pivot date --Time --- Inserted date
-----------9:00 PM 19-Sep
----------10:00 PM 19-Sep
--------- 11:00 PM 19-Sep
20-Sep 12:00 AM 9/20/2004
--------- 1:00 AM 9/20/2004

I inserted a column after the time. The formula was placed in th
inserted date column =IF(S29=TIME(24,0,0),T28+1,T28) Where Column "S
was the time and Column "T" was the date. In plain words.... th
formula says IF the time is = to midnight then increment the date b
one, otherwise keep the same date as the cell above.

This works but... it's seems like there should be an easier way.

Let me know if you find it