As illustrated at John Walkenbach's website :
http://j-walk.com/ss/excel/usertips/tip068.htm
If your data is actually in separate cells....like this:
Date___Time_1_____Time_2____Time_3____Time_4____Time_5____Time_6
8-Aug____2:42____(blank)___(blank)___(blank)___(blank)___(blank)
8-Aug____7:29_______8:10______9:58_____10:45_____11:58_____13:33
9-Aug____4:57____(blank)___(blank)___(blank)___(blank)___(blank)
9-Aug____6:28_______9:46_____10:32_____11:28_____12:44_____13:44
<Data><Pivot Table>
Use: Multiple Consolidation Ranges_____Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]_____Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]
That will create a minimal Pivot Table containing only one cell with a
value.
Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row___________Column______Value
8/8/2007______Time_1______2:42:00 AM
8/8/2007______Time_2______(blank)
8/8/2007______Time_3______(blank)
8/8/2007______Time_4______(blank)
8/8/2007______Time_5______(blank)
8/8/2007______Time_6______(blank)
8/8/2007______Time_1______7:29:00 AM
8/8/2007______Time_2______8:10:00 AM
8/8/2007______Time_3______9:58:00 AM
8/8/2007______Time_4______10:45:00 AM
8/8/2007______Time_5______11:58:00 AM
8/8/2007______Time_6______1:33:00 PM
8/9/2007______Time_1______4:57:00 AM
8/9/2007______Time_2______(blank)
8/9/2007______Time_3______(blank)
8/9/2007______Time_4______(blank)
8/9/2007______Time_5______(blank)
8/9/2007______Time_6______(blank)
8/9/2007______Time_1______6:28:00 AM
8/9/2007______Time_2______9:46:00 AM
8/9/2007______Time_3______10:32:00 AM
8/9/2007______Time_4______11:28:00 AM
8/9/2007______Time_5______12:44:00 PM
8/9/2007______Time_6______1:44:00 PM
Then just delete the blank rows.
Is that something you can work with?
--------------------------
Regards,
Ron (XL2003, Win XP)
Microsoft MVP (Excel)