Excel - Pivot Tables - HELP!

cat_with_no_tail

So I have a metric fuckton of information I need to sort into a pivot table and arrange into date order (easy enough) but I also need to break this info down for week-by-week analysis.

There are in the region of 200k rows.
Just to complicate matters, the date which is read directly off our internal systems displays like "November 5, 2010 21:21:22"

Anyone know how I would go about making collapsable boxes to break the data into days and then further into week-by-week.

It can be calendar weeks, or just mon-sun.

:? :shock:

bails87

Can you not get the date into a recognisable format? Then excel should realise they're dates and let you break it down by year/month/day.

cat_with_no_tail

AHA! Got it!

In order to convert the date and time as it appears above, I did a macro.

Ran a new module in VB with the following code:

Sub ConvertToDateTimes()
Dim Cell As Range, S As String
For Each Cell In Selection
S = Replace(Cell.Value, " ", " ")
If Right(S, 2) Like "[AaPp][mM]" Then
S = Left(S, Len(S) - 2) & " " & Right(S, 2)
End If
If IsDate(S) Then Cell.Value = CDate(S)
Next
End Sub

That's converted them all to "proper" excel date/time format, which I can then alter to display as I want it. The times are still there, they are just hidden until you open the cell.

Man it's taken me AGES to do that!