Forum home Mountain biking forum The hub

Excel - Pivot Tables - HELP!

Cat With No TailCat With No Tail Posts: 13,581
edited May 2011 in The hub
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:

Posts

  • bails87bails87 Posts: 13,317
    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.
    MTB/CX

    "As I said last time, it won't happen again."
  • Cat With No TailCat With No Tail Posts: 13,581
    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!
Sign In or Register to comment.