Hour, Minute, Second formatting in Pivot Table

[brianfitz]

Hi guys, hope you can help.

I have a sheet containing all of my pivot table data which is exported from our internal phone system. It contains stuff like handling time of calls, average call durations etc but the data is stored in seconds. For instance 8162 seconds is 6hrs 52 minutes. I get this figure by editing the cell formula to read =8162/60/60 and then changing the Cell Format to Custom and hh:mm:ss.

My question is I have over 2000 lines of this data, which increases by 2000 every month, and the above method of finding my hh:mm:ss is impractial to do as there dozens to do per line (meaning editing potenially 30,0000 individual cells.) is there a way that I can convert all of the time cells to this format and formula?

Sorry if I'm not being very clear but I appreciate any help you may be able to offer.

Many thanks,

Brian

 

[Andrew Poulsom]

Welcome to the Board.

Sorry, no. You can't format a cell with decimal seconds as a time. You have to convert it to a serial time with a formula.

You could use a macro to do the conversion for you.

 

[brianfitz]

Thanks very much for your reply Andrew. As I don't really have much experience/success with macros could you recommened any resources which might point me in the right direction?

 

[Andrew Poulsom]

What's the name of your sheet and in which column are your times in seconds?

 

[brianfitz]

Name of the sheet is sheet1 and the columns are D, E, F, G, I, J, K, L, M, N, P, R, Z, AA and columns AP and AQ are dates but I'm not sure this is relevant.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LR As Long
    Dim r As Long
    With Worksheets("Sheet1")
        If .Range("D2").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = 2 To LR
            With .Range("D" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("E" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("F" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("G" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("I" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("J" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("K" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("L" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("M" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("N" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("P" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("R" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("Z" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("AA" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
        Next r
    End With
End Sub

 

[brianfitz]

Wow, thanks so much for that, I really appreciate it! it did seem to start off well enough however it did come up with an error, possibly because the data doesn't start in these columns until line 6? As in the first piece of timing data is D6.

It did try to run the macro and it did come up with timing data but in the empty cells at d1.

Any ideas?

 

[Andrew Poulsom]

Try changing:

For r = 2 To LR

to:

For r = 2 To LR

 

[brianfitz]

Try changing:

For r = 2 To LR

to:

For r = 2 To LR

hi Andrew, don't mean to sound petulant but those two seem identical!

 

[Andrew Poulsom]

Sorry I meant:

For r = 6 To LR