Hour, Minute, Second formatting in Pivot Table

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Aug 6, 2010
Messages
18
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?
 

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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

New Member
Joined
Aug 6, 2010
Messages
18

ADVERTISEMENT

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,454
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top