pivot table -repeating "months or days" after grouping

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??

--
Message posted from http://www.ExcelForum.com

0
9/13/2004 9:20:14 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
297 Views

Similar Articles

[PageSpeed] 23

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 <" wrote:
> 
> I have data that includes the month, day, hour, min.  When I run the
> pivot table function to reduce the data to hourly I need the date to
> 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???
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/13/2004 10:59:04 PM
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

--
Message posted from http://www.ExcelForum.com

0
9/13/2004 11:34:16 PM
Reply:

Similar Artilces: